JChandler22,

Your original data is on sheet1, and the resulting data is on sheet2.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.


Option Explicit
Sub AdjustData()
    Dim LR&, LR2&, Ctr&, Ctr2&, MaxCol&
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        LR& = Cells(Rows.Count, "A").End(xlUp).Row
        With .Range("C1:C" & LR&)
            .FormulaR1C1 = "=LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],"","","""")) + 1"
            .Value = .Value
        End With
        .Range("B1:B" & LR&).Copy .Range("D1")
        With .Range("D1:D" & LR&)
            .TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
        End With
        LR2& = 1
        MaxCol& = 0
        For Ctr& = 1 To LR& Step 1
            Ctr2& = Range("C" & Ctr&).Value
            If Ctr2& > MaxCol& Then MaxCol& = Ctr2&
            .Range("A" & Ctr&).Copy Sheets("Sheet2").Range("A" & LR2& & ":A" & LR2& + Ctr2& - 1)
            .Range(Cells(Ctr&, 4), Cells(Ctr&, 4 + Ctr2& - 1)).Copy
            With Sheets("Sheet2").Range("B" & LR2&)
                .PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
                Application.CutCopyMode = False
            End With
            LR2& = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Next Ctr&
        Range(Cells(1, 3), Cells(LR&, 3 + MaxCol&)).ClearContents
    End With
    Sheets("Sheet2").Select
    Range("C1").Select
    Application.ScreenUpdating = True
End Sub

Then run the "AdjustData" macro.


Have a great day,
Stan