Pivoting repeated strings

[camchocho]

Sorry if the title isn't descriptive I honestly had no clue how to quickly say what I'm working on.Hello, thanks very much in advanced for the help. Basically I have a long list with two columns, the first column has a word and the second a string of numbers. I would like to automatically re-arrange it so that any time something from column A is repeated it is removed but it's number string from column B is added to a new column next to the first time the word popped up in column A. Note that I want exact matches only, since there are very similiar word strings in some of column A.

Here is an example; the top is what my data looks like now, and the bottom is what I would like to be able to do with it. I had thought of doing a conditional formatting for repeats and then a pivot-paste kind of thing, but there's about 10000 strings in my sheet so automation would be very helpful.

<tbody>
[TD="align: left"]AA[/TD]
[TD="align: right"]123[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA[/TD]
[TD="align: right"]124[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA[/TD]
[TD="align: right"]125[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA[/TD]
[TD="align: right"]126[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA2[/TD]
[TD="align: right"]127[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA2[/TD]
[TD="align: right"]128[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA3[/TD]
[TD="align: right"]129[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA3[/TD]
[TD="align: right"]130[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BB[/TD]
[TD="align: right"]131[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BB[/TD]
[TD="align: right"]132[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BB[/TD]
[TD="align: right"]133[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BC[/TD]
[TD="align: right"]134[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BC[/TD]
[TD="align: right"]135[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BC[/TD]
[TD="align: right"]136[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BE[/TD]
[TD="align: right"]137[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]126[/TD]

[TD="align: left"]AA2[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]128[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]AA3[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]130[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]BB[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]133[/TD]
[TD="align: left"][/TD]

[TD="align: left"]BC[/TD]
[TD="align: right"]134[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]136[/TD]
[TD="align: left"][/TD]

[TD="align: left"]BE[/TD]
[TD="align: right"]137[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

</tbody>

 

[Fennek]

Hello,

from myarchiv w/o adaption.

Sub iFen()
Ar = Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
    For i = 2 To UBound(Ar)
        If .exists(Ar(i, 1)) Then
            .Item(Ar(i, 1)) = .Item(Ar(i, 1)) & ", " & Ar(i, 2)
        Else
            .Item(Ar(i, 1)) = Ar(i, 2) 
        End If
    Next i

Cells(5, 1).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
End With
End Sub

I hope you can debug it.

regards