How To re-organize data

Dan,

rylo asked a good question, one I didn't think about. I assumed the data was spread across the row, with each comma denoting a new cell. I finished a script and so figured I would post it in case that assumption was correct (Ignore this post if it wasn't).

Change the StartRow to the first row containing data. I set it to 2 assuming row 1 was headers and the data started in row 2.

Sub tgr()
    
    Const StartRow As Long = 2
    
    Dim arrData() As Variant
    ReDim arrData(1 To 4, 1 To Rows.Count)
    Dim DataIndex As Long
    Dim RowIndex As Long
    Dim ColIndex As Long
    
    For RowIndex = StartRow To Cells(Rows.Count, "A").End(xlUp).Row
        For ColIndex = 2 To Cells(RowIndex, Columns.Count).End(xlToLeft).Column Step 3
            DataIndex = DataIndex + 1
            arrData(1, DataIndex) = Cells(RowIndex, "A").Value
            arrData(2, DataIndex) = Cells(RowIndex, ColIndex + 0).Value
            arrData(3, DataIndex) = Cells(RowIndex, ColIndex + 1).Value
            arrData(4, DataIndex) = Cells(RowIndex, ColIndex + 2).Value
        Next ColIndex
    Next RowIndex
    
    If DataIndex > 0 Then
        ReDim Preserve arrData(1 To 4, 1 To DataIndex)
        With Sheets.Add(after:=Sheets(Sheets.Count))
            .Name = "Cleaned Data"
            .Range("A2:D2").Resize(DataIndex).Value = Application.Transpose(arrData)
            With .Range("A1:D1")
                .Value = Array("Item Code", "Component #", "Qty", "Cost")
                .Font.Bold = True
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .EntireColumn.AutoFit
            End With
        End With
    End If
    
End Sub