Convert Columns to row variable data
 

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

Convert Columns to row variable data
 

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


"David" wrote in message
...
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

Convert Columns to row variable data
 

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


"David" wrote in message
...
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




.