Convert Columns to row variable data

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hi

I need to convert column data into row data

From this

Name Address
Fred 30 Jones St
London
Sue Tower 42
London
EC2

To this

Name Address1 Address2 Address3
Fred 30 Jones St London
Sue Tower 42 London EC2


I have a macro that can convert but the problem is that
each person address is has a variable amount of fields.
Any way round this

Thanks for any help

David
 
David,

Assuming your data is in Columns A and B, and starts in Row 2, in cell
C2, use the formula

=IF(AND($A2<>"",OFFSET($A2,COLUMN()-2,0)="",B2<>""),OFFSET($A2,COLUMN(
)-2,1),"")

Copy as far right as needed, and as far down as your data goes.

Then copy all the formula and pastespecial values, then select column
A and use Edit | Go To... Special.... Blanks OK, and then delete
entire row.

And you're done.

HTH,
Bernie
 
Thanks that works perfectly !!

david

-----Original Message-----
David,

Assuming your data is in Columns A and B, and starts in Row 2, in cell
C2, use the formula

=IF(AND($A2<>"",OFFSET($A2,COLUMN()-2,0)="",B2<>""),OFFSET ($A2,COLUMN(
)-2,1),"")

Copy as far right as needed, and as far down as your data goes.

Then copy all the formula and pastespecial values, then select column
A and use Edit | Go To... Special.... Blanks OK, and then delete
entire row.

And you're done.

HTH,
Bernie





.
Click to expand...
 
Back
Top