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.

Answer 2

You could apply the process detailed here but finish off with addressing those data rows that are shorter than others (fewer cells populated) by filtering Value to select (Blanks) and deleting those rows.