Two Column Transpose by unique value in A

[Jeffrey Green]

Not sure if my Title is good, but here is what i have.
Two Columns, A and B
Column A is a list 15,000 Rows long, with about 1300 unique values (Entry Numbers)\
Column B is a list of Row Numbers on those entries in Column A.
Sample:


[TABLE="width: 169"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ENTRY NUMBER[/TD]
[TD]LINE #[/TD]
[/TR]
[TR]
[TD]30027219236[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]30027219236[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]30027219236[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]30027219236[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]30027219236[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]30027219236[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

But now they want us to show the data like this:

[TABLE="width: 290"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Entry [/TD]
[TD]Line[/TD]
[/TR]
[TR]
[TD]Sample output[/TD]
[TD]30027219236 [/TD]
[TD] 7,8,16,28,29,etc[/TD]
[/TR]
</tbody>[/TABLE]

(All on same line) For each of the 1300 entries.

A macro would be nice, but a formula/process i can understand is ok too

Thanks , this is a tough one for me

 

[Jeffrey Green]

Actually, to be more clear, i dont need the commas etc. I can live with each row being in its own cell . . . .if there are 10 rows on an entry, i can have 10 columns out. I think that is better. thanks

 

[rpaulson]

Jeffery,

Try this on a copy of your file.

If will start writing your entries in cell E2 and the results starting in column F

Sub do_it()

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

entry = Cells(r, "A")
Line = Cells(r, "B")

If WorksheetFunction.CountIf(Range("E:E"), entry) = 0 Then 'this is a new entry
    wr = Range("E" & Rows.Count).End(xlUp).Row + 1
Else
 wr = WorksheetFunction.Match(entry, Range("E:E"), 0) 'Entry exists
End If

lc = Cells(wr, Columns.Count).End(xlToLeft).Column + 1

Cells(wr, "E") = entry
Cells(wr, lc) = Line
Next r

End Sub

hth,

Ross

 

[Jeffrey Green]

Solved (with minor issue that i fixed in Excel. It put the first row number in Column C, the Entry number in D then the rest of the row numbers correctly.
I just inserted a column between the Entry number and the first column containing row numbers and move column C over.

You saved my day, sincerest thanks !