Convert Column to row with variable data

D

David Randall

Hi

I'm using Excel 2k and I have a spreadsheet that looks
like this

Name Address
Fred 21 Blah St
London
Sue Tower 50
London
EC2

and need it to look like this

Name Address 1 Address2 Address3
Fred 21 Blah St London
Sue Tower 50 London EC2

I have a macro that can convert from column to row but
only for a set number of columns. Is there any way to
account for the variable amount of data for each address ?

Any help much appreciated

Thanks
David
 
G

GB

David:

I suggest the following formulae - copied down as necessary:

C2: =IF(ISBLANK(A2),C1+1,1)
D2: =IF(C2=1,A2,"")
E2: =IF(C2=1,B2,"")
F2: =IF(C3=2,B3,"")
G2: =IF(C4=3,B4,"")
H2, I2, J2: similar ....


This sorts your data into rows with blank rows in between. Then you can use
a filter or sort to get rid of the blank rows.

This can also be done in VBA, but the above seems simple enough.

HTH

Geoff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert Columns to row variable data 2
Mailing address block: sorting 1 row to columns 1
reverse a pivot back to original data state 2
Count values in column only if value of another column is true 4
convert column to row 2
Sorting 5 columns of data based on column A 6
Macro to convert email address to web address? 4
Macro to convert some columns to rows 3

Top