Conditionally Transpose

Question

I have a table that looks like this:

SKU    ID1    ID2    ID3    ID4     and so on...
111    1ab    2bc    3bc                                
222    1bb    3bb    4bb    abb
333    2bb    3bb    4bc    abc
444    1b2    2bb

I am trying to format all of the data as below, hopefully with some kind of macro:

SKU    ID
111    1ab                      
111    2bc
111    3bc  
222    1bb
222    3bb
222    4bb
222    abb
333    2bb
333    3bb
333    4bc
333    abc
444    1b2
444    2bb

I have tried transposing all of it at once, but it does not come out right. I think a more coded macro type solution is needed, but I am not sure.

Answer 1

Here is an image of Sheet1:

Here is the code in Sheet1's module:

Public Sub ProcessData()
    Dim rData As Range
    Dim OutRow As Long
    Dim DataRow As Long
    Dim DataCol As Long
    Dim SKU As Long

    With Worksheets("Sheet1")
        Set rData = .Range("D1:J10") ' deliberately larger than the data, as a deminstration
        OutRow = 1
        For DataRow = 2 To rData.Rows.Count
            SKU = rData(DataRow, 1)
            If SKU = 0 Then
                Exit For
            End If
            For DataCol = 2 To rData.Columns.Count
                If Not IsEmpty(rData(DataRow, DataCol)) Then
                    OutRow = OutRow + 1
                    .Cells(OutRow, 1) = SKU
                    .Cells(OutRow, 2) = rData(DataRow, DataCol)
                Else
                    Exit For
                End If
            Next
        Next
    End With
End Sub

This should give you a good starting point.