Consolidate data in multiple columns

Hi Mr Phil

Try this

Please Login or Register  to view this content.

Cordialy

Using Power Query it is a simple case of unpivotting the data. Here is the Mcode to achieve that and the file is attached for your review. No coding required. All achieved in the UI.

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

According to the attachment another VBA beginner starter just using a single loop, result in sheet #2 :

PHP Code: 

Sub Demo1()
    Dim L&, R&, C%
        L = 2
        Application.ScreenUpdating = False
        If Worksheets.Count = 1 Then Sheets.Add , Sheets(1) Else Sheets(2).UsedRange.Clear
    With Sheets(1).[A1].CurrentRegion
           .Range("A1:C1").Copy Sheets(2).[A1]
        For R = 2 To .Rows.Count
            C = .Cells(R, 1).End(xlToRight).Column - 3
           .Cells(R, 1).Resize(, 3 - (C = 1)).Copy Sheets(2).Cells(L, 1).Resize(C)
            If C > 1 Then Sheets(2).Cells(L, 4).Resize(C) = Application.Transpose(.Cells(R, 4).Resize(, C))
            L = L + C
        Next
    End With
    With Sheets(2).UsedRange.Columns
        .NumberFormat = "_wGeneral_w;;;_w@_w"
        .Cells(4) = "PART NEEDED"
        .AutoFit
        .Sort .Item(3), xlAscending, .Item(2), , xlAscending, , , xlYes
    End With
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

I think this is really interesting. I’m going to have to study on it. I’ve never used power query. But it looks intriguing. Is it an add in?

Originally Posted by alansidman

Using Power Query it is a simple case of unpivotting the data. Here is the Mcode to achieve that and the file is attached for your review. No coding required. All achieved in the UI.

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

https://www.youtube.com/watch?v=gwW2CDdvUUs

Also, look at the link in my signature block. It is a very cool and powerful feature of Excel. A good primer is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.