VBA to tranpose - stops at Column E

[s00shi]

Hi there,

This is probably really simple, but.. I can't figure it out so far

I have a large chunk of data that looks something like this:

Mary CS208515
Mary CS208518
Mary CS308520
Susan HE501924
Lisa DE504210
Laura DE504211
Laura DE504212
Cheryl ED503360

I want this to be arranged in rows, one for each of the values in Column A, like so:

Mary CS208515 CS208518 CS308520
Susan HE501924
Lisa DE504210
Laura DE504211 DE504212
Cheryl ED503360

etc, etc

Some of the unique numbers in column A appear once, twice, 12, or many more times.

I've found a VBA script that will almost do it:


Sub lineemup()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) = Cells(i + 1, 1) Then
nc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
Cells(i + 1, 2).Resize(, nc).Copy Cells(i, nc)
Rows(i + 1).Delete
End If
Next i
End Sub

This transposes the data, but it all truncates at column E, even if there are - for instance - 12 entries.... and I can't figure out why.

Can anyone help?

[Andrew-R]

What I'd do is:

Please Login or Register  to view this content.

[s00shi]

That's worked perfectly, thanks so much!

I notice it's quite different to the one I had found - I had hoped I could edit a little bit here, a little bit there and have something to suit my needs... guess I should learn VB properly!

[Andrew-R]

I played around with your original code for a little while, but couldn't make it work for me, so I figured the easiest option was to just change it to work the way I'd have written it from scratch.

In theory your code is more flexible, because it attempts to detect which column it's writing to, rather than have it semi-hard-coded, but that was also the bit I was having problems getting to work.

Hopefully my code is fairly straightforward, but give a shout if you want some details on what it does.