Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to convert a table of data to columns only | Excel Discussion (Misc queries) | |||
Convert muliple data into columns | Excel Discussion (Misc queries) | |||
Convert 1 row of data into Multiple columns | Excel Discussion (Misc queries) | |||
How do I convert some data in the row to columns? | Excel Discussion (Misc queries) | |||
Convert data from rows to columns | Excel Discussion (Misc queries) |