Forum Discussion
ChrisRopeta
Mar 31, 2021Copper 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 ...
HansVogelaar
Mar 31, 2021MVP
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- ChrisRopetaMar 31, 2021Copper Contributor
Thank you so mailto:much@!