I suspect problem was the line-wrap when you copied from the NG.

Anway, glad it's solved and working OK.

Thanks for the feedback.

"AdrianCoyte" wrote:

Hey Toppers,

I fixed it. Many Many Many thanks.

Genius.


Adrian


"Toppers" wrote:

Try this macro: assumes data on Sheet1 is rotated on Sheet2

Sub Rotate()

Dim nextrow1 As Long, nextrow2 As Long
Dim lastrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

nextrow1 = 1
nextrow2 = 1

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")


With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Do
n = Application.CountIf(.Range("A:A"), .Cells(nextrow1, "A"))
.Cells(nextrow1, "A").Copy ws2.Cells(nextrow2, "A")
.Cells(nextrow1, "B").Resize(n, 1).Copy
ws2.Cells(nextrow2, "B").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, _ SkipBlanks:= False, Transpose:=True
nextrow1 = nextrow1 + n
nextrow2 = nextrow2 + 1
Loop Until nextrow1 lastrow
End With
End Sub

HTH

"AdrianCoyte" wrote:

I have data (lots of it - 200k lines) that looks like this:

Row/Column A B
1 RABBIT 123
2 RABBIT 887
3 RABBIT 455
4 PIG 336
5 PIG 723

I want this to look like:
Row/Column A B C D
1 RABBIT 123 887 455
2 PIG 336 723


I know that I can transpose each one in turn, but that would take weeks.

Is there someone clever out there ?