How to Transfer and combine data from one sheet to another

How to transfer and combine data from one sheet to another

Requirement:

The user is trying to convert the data on Sheet2:

https://image.ibb.co/bZSQYQ/1.png

Into this format on Sheet 3:

https://image.ibb.co/di8onk/2.png

The user also attached a sample file.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148868-transfer-and-combine-data-from-one-sheet-to-another

Solution:

Click the button on Sheet 1 to update the JUN17 Table.

Code assigned to button is:

Code:

Sub UpdateConsolidated()
    Dim x, y, i As Long, ii As Long
    
    x = Sheets("Sheet1").Cells(1).CurrentRegion '// Change sheet name to suit
    ReDim y(1 To UBound(x, 1) - 1, 1 To 6)
    For i = 2 To UBound(x, 1)
        y(i - 1, 1) = x(i, 1): y(i - 1, 2) = x(i, 3): y(i - 1, 4) = x(i, 2)
        For ii = 4 To UBound(x, 2)
            If x(i, ii) <> "" Then
                If y(i - 1, 6) = "" Then
                    y(i - 1, 6) = x(i, ii) & "-" & x(1, ii)
                Else
                    y(i - 1, 6) = y(i - 1, 6) & " / " & x(i, ii) & "-" & x(1, ii)
                End If
            End If
        Next
    Next
    With Sheets("JUN17")
        i = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(i, 1).Resize(UBound(y, 1), UBound(y, 2)) = y
        .Rows.AutoFit
        .Activate
        ActiveWindow.ScrollRow = i + UBound(y, 1) - 5
    End With
        
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.