Thread: Rows into column

Going with that layout, make sure your HEADERS are in the OUTPUT sheet the way you have now, then run this, it will put the items in matching order:

Option Explicit

Sub ReformatData()
Dim LR As Long

With Sheets("Input")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("B2:B" & LR).FormulaR1C1 = _
        "=IF(LEFT(TRIM(RC[-1]),4)=""File"", TRIM(RC[-1])& ""-""&COUNTIF(R2C1:RC1,RC1), """")"
End With

With Sheets("Output")
    LR = Int(LR / 6) + 1
    .UsedRange.Offset(1).EntireRow.ClearContents
    With .Range("A2:F" & LR)
        .FormulaR1C1 = "=INDEX(Input!C1, MATCH(TRIM(R1C)&""-""&ROW(R[-1]C1), Input!C2, 0)+1)"
        .Value = .Value
        .SpecialCells(xlConstants, xlErrors).ClearContents
    End With
End With

Sheets("Input").Range("B:B").ClearContents
End Sub