Converting rows to columns over 3400 rows with different group sizes

[StaceyB2013]

I have a spreadsheet that contains order numbers and line items on each order. What I need to do is transpose and concatenate the column with the line item positions.

Example:

Column A Column B
order # pos #
10256 10
10256 20
10256 30
10256 40
10335 190
10335 200
10335 300
10410 10
10410 20
10560 10
10560 20
10560 50
10560 60

What I need it to look like:
10256 10, 20, 30, 40
10335 190, 200, 300
10410 10, 20
10560 10, 20, 50, 60

I know how to transpose and concatenate; and how to do a combination of the two but there are over 3400 rows on this sheet and to try to manually transpose each set is not feasible.

Can anyone help me learn how to transpose and concatenate for each series of order numbers?

... and I'm working in Excel 2003

Thank you very much

 

[pboltonchina]

Try this on a copy of your dat to see if it does what you want

Option Explicit


Sub Consolidate()
'JBeaucaire (9/18/2009)
'Columnar data is Sorted/Matched by column A values, merge all other cells into row format
Dim LastRow As Long, NextCol As Long
Dim LastCol As Long, Rw As Long, Cnt As Long
Dim DelRNG As Range
Application.ScreenUpdating = False


'Sort data
LastRow = Range("A" & Rows.count).End(xlUp).Row
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

'Seed the delete range
Set DelRNG = Range("A" & LastRow + 10)

'Group matching names
For Rw = LastRow To 2 Step -1
If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
Range(Cells(Rw, "B"), Cells(Rw, Columns.count).End(xlToLeft)).Copy _
Cells(Rw - 1, Columns.count).End(xlToLeft).Offset(0, 1)
Set DelRNG = Union(DelRNG, Range("A" & Rw))
End If
Next Rw


'Delete unneeded rows all at once
DelRNG.EntireRow.Delete (xlShiftUp)
Set DelRNG = Nothing


'Add titles
NextCol = Cells(1, Columns.count).End(xlToLeft).Column + 1
LastCol = Cells(1, 1).CurrentRegion.Columns.count
Range("B1", Cells(1, NextCol - 1)).Copy Range(Cells(1, NextCol), Cells(1, LastCol))


Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

 

[StaceyB2013]

I believe you have done it! Thank you very much!