New Contributor

# Excel Columns to Rows with a hitch

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

3 Replies

# Re: Excel Columns to Rows with a hitch

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
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``````

# Re: Excel Columns to Rows with a hitch

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

where 'number'

= UNIQUE(Order)