macro to transpose cells in Column B based on unique values in Column A

[Aaron J.]

Greetings,

As a less-than-subtle disclaimer, I am a newcomer to VBA programming,
and I apologize for not having had the time to look into my (fairly
pressing) problem much on my own before casting this general inquiry
to the forum:

Does anyone know of a macro that would allow me to iteratively
transpose series of cells in one column as based unique values in
another column? As an example, I need to get from this:

A B
1 17
1 90
1 96
1 6
2 12
2 10

to this:

A B C D E
1 17 90 96 6
2 12 10

where column A contains the unique values by which values in column B
are transposed.

(My spreadsheet contains thousands of rows. There would be enough
columns for the transpositions, however, because no more than 256
instances occur for any given value in column A.)

In lieu of an extant macro that would do this, I would, of course, be
interested to know if anyone could help me determine what other code I
might attempt to patch together toward this end.

Any help or ideas on this would, of course, be most appreciated!

Aaron J.

 

[Cecilkumara Fernando]

Aaron,
Try this macro
Cecil

Sub MacroToFilter()
Dim LRow As Long
Dim LRowU As Long
Dim i As Long
Dim crit As String
Application.ScreenUpdating = False
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), Unique:=True
LRowU = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LRowU
crit = Range("C" & i).Value
Range("A1:B" & LRow).AutoFilter Field:=1, _
Criteria1:=crit
With Range("B2:B" & LRow)
.SpecialCells(xlCellTypeVisible).Copy
Range("D" & i).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False
Next i
Range("A1:B" & LRow).AutoFilter
Application.ScreenUpdating = True
End Sub

 

[Aaron J.]

Cecil,

Your code worked exquisitely!

(The first node-to-node adjacency needed to be manually re-inserted
into the output, but that's far beside the point -- you've spared me
long hours of Sisyphusian tedium.) Thankyou, as well as for being so
quick in responding to my inquiry.

My sincere gratitude,
Aaron J.

 

[Aaron J.]

Cecil,

Your code worked exquisitely! - My sincere gratitude.

Thankyou as well as for responding so quickly to my inquiry.

Aaron