Excel Columns to Rows with a hitch

ChrisRopeta 

An Excel 365 worksheet formula generating a single row of the output table:

where 'number'

= UNIQUE(Order)

ChrisRopeta 

Here is a macro that you can run:

Sub Transform()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim c As Long
    Dim n As Long
    Application.ScreenUpdating = False
    Set w1 = ActiveSheet
    Set w2 = Worksheets.Add(After:=w1)
    w2.Cells(1, 1).Value = "Order #"
    t = 1
    m = w1.Cells(1, 1).End(xlDown).Row
    For s = 2 To m
        If w1.Cells(s, 1).Value <> w1.Cells(s - 1, 1).Value Then
            t = t + 1
            w2.Cells(t, 1).Value = w1.Cells(s, 1).Value
            c = 0
        End If
        c = c + 2
        If c > n Then n = c
        w2.Cells(t, c).Value = w1.Cells(s, 2).Value
        w2.Cells(t, c + 1).Value = w1.Cells(s, 3).Value
    Next s
    For c = 2 To n Step 2
        w2.Cells(1, c).Value = "Resource Assigned #" & c / 2
        w2.Cells(1, c + 1).Value = "Resource #" & c / 2 & " Role"
    Next c
    w2.UsedRange.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub