reformat address info
Posted by James King on July 16, 2001 9:00 PM
I have several excel files that contain address information in the following format:
Cell A1:Name
Cell B1:Address
Cell C1:City
Cell D1:blank
repeat...
I want to reformat the information so Column A is Name, Column B is Address and Column C is City. Currently, I am cutting and pasting from the rows to the columns but it takes too much time. Is there a way to automate this reformatting? I tried to record a macro, my first time, but the macro was specific to the cells I was reformatting. If I could position the cursor in a cell and run a macro from there, that would be a step in the right direction. I hope my request is clear. Thanks for any help.
Jim
Posted by Aladin Akyurek on July 17, 2001 2:03 AM
Jim,
You might want to try the following. if you don't have any duplicates
Lets take the sample that follows:
{"n1";"a1";"c1";0;"n2";"a2";"c2";0;"n3";"a3";"c2";0;"n4";"a4";"c4";0;"n5";"a5";"c5"} [ where 0 stands for blank ]
The above data occupy A1:A19.
In B1 enter: =ADDRESS(ROW(),1)
In C1 enter: =ADDRESS(ROW()+1,1)
In D1 enter: =ADDRESS(ROW()+2,1)
In B2 enter: =ADDRESS(ROW(INDIRECT(B1))+4,1)
Copy the formula in B2 first to C2:D2 then down until the cell address of the last cell in column A appears in column D.
In E1 enter: =INDIRECT(B1)
Copy the formula in E1 first to F1:G1 then down up to last row of data in column D.
Now select the range of data in F to G, do a copy and Paste Special|Values. Then delete columns A to D.
Aladin
===============
Posted by Aladin Akyurek on July 17, 2001 2:07 AM
Typo ...
