Moving data from columns to rows

[brittben17]

Ok, a faily simple problem that I can't quite get my wee little brain around.

I have the following spreadsheet with 4 columns of data:

1000001 245 a Mr Fred Blogs
1000001 270 a 1 Blog Street
1000001 270 b Norwich
1000001 270 c Norfolk
1000001 270 e NRx x99
1000001 270 g The Manager
1000001 270 k 01603 555 555
1000001 270 l 01603 555 555
1000001 270 q Manager
1000001 270 x N
1000001 270 y N
1000002 245 a Mrs Fredilina blogs
1000002 270 a 10 Blog Street
1000002 270 b Norwich
etc etc for about 36,000 rows.

What I need is for each unigue reference ID (column A), I need all corresponding data moved up into a single row in serperate columns, something like this:

1000001 |245| a| Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc

Each entry doesn't nessesarily have the same number of rows.

I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.

Hope this all makes sense.

Any ideas gratefully recieved.

Ben

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[brittben17]

Indeed! - A good idea.

Please find attached workbook which will hopefully describe a little easier what I am looking for.

Cheers,

Ben

[StephenR]

Here is one approach:

Please Login or Register  to view this content.

[brittben17]

Thanks Stephen..

I am getting '424' Object required pop-ups with

Please Login or Register  to view this content.

highligted by the debugger....

Any thoughts?

Ben

[StephenR]

Ben - it worked for your example, but perhaps your actual file is different. Try using sheet names, e.g. replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[brittben17]

Stephen,

You are a star - many thanks for this - I'll enjoy unpicking the code to work out whats going on, but otherwise, top job!


Regards,

Ben