Excel Columns to Rows with a hitch

[ChrisRopeta]

I have Sales Order Numbers in column A.  In column B are names of multiple people who work on the sales order and in column C are roles of the people who are listed in column B.  I need help to find a way by order number to move the names and roles of the people in columns B and C to a row with the sales order number still in column A.   Please see example below.  If there are any formulas or other techniques that you could suggest, that would be greatly appreciated!  

Existing Information in Columns/Rows
Order #	Resource Assigned	Resource Role
5523	John Smith	Sales Assistant
5523	Tammy Brown	Account Manager
5523	Felicia Huff	Purchasing Agent
4029	Tammy Brown	Account Manager
4029	Felicia Huff	Purchasing Agent
4029	Jason Wilowski	Sales Assistant
3677	Chris Ericson	Account Manager
3677	Bob Welsh	Purchasing Agent
Desired Final View
Order #	Resource Assigned #1	Resource #1 Role	Resource Assigned #2	Resource #2 Role	Resource Assigned #3	Resource #3 Role
5523	John Smith	Sales Assistant	Tammy Brown	Account Manager	Felicia Huff	Purchasing Agent
4029	Tammy Brown	Account Manager	Felicia Huff	Purchasing Agent	Jason Wilowski	Sales Assistant
3677	Chris Ericson	Account Manager	Bob Welsh	Purchasing Agent

Chris

[#2]

@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

[#3]

@Hans Vogelaar 

Thank you so much!  I will give it a try!

[#4]

@ChrisRopeta 

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

where 'number'

= UNIQUE(Order)