Thought I'll list here the salient steps
(for the benefit of all)
Assume the data is in col A, from row2 down
(with a label / col header in A1)
Example: Data is in groups of 5 lines each, viz.:
<Label>
Name1
add1
city1
state1
zip1
Name2
add2
city2
state2
zip2
Steps
--------
1. Remove all in-between blank rows
-------------------
Select col A
Press F5 > Special > Check "Blanks" > OK
(this will select all the blank cells in col A)
Right-click (on any selected part) > Delete > Check "Entire Rows" > OK
The above will delete all the blank rows, resulting in:
<Label>
Name1
add1
city1
state1
zip1
Name2
add2
city2
state2
zip2
---------------------
With the data starting in *row2* down (i.e. in A2 down)
2. Put in B2: =INDIRECT("A"&5*ROW()-8+COLUMN()-2)
3. Copy B2 across to F2, then copy down until zeros appear
The data in col A will be re-arranged row-wise into B2:F2, viz.:
Name1...add1...city1...state1...zip1
Name2...add2...city2...state2...zip2
etc
-------------------------------
4. If needed, freeze the values in cols B to F
-------------------------------------------------------------
Select cols B to F
Right-click > Copy
Right-click > Paste Special > Check "Values" > OK
5. Delete col A (if desired)
-------------------------------------
Note: Adjustments for other # of lines per group
---------------------------------------------------
If the data is in groups of 2, 3, 4, or 6 lines, instead of the 5 lines
above,
just replace the formula in B2 by:
For 2 lines per group: =INDIRECT("A"&2*ROW()-2+COLUMN()-2)
For 3 lines per group: =INDIRECT("A"&3*ROW()-4+COLUMN()-2)
For 4 lines per group: =INDIRECT("A"&4*ROW()-6+COLUMN()-2)
For 6 lines per group: =INDIRECT("A"&6*ROW()-10+COLUMN()-2)
For 7 lines per group: =INDIRECT("A"&7*ROW()-12+COLUMN()-2)
copy accordingly across as many columns as the # of lines per group,
then copy down until zeros appear
--
hth
Max
-----------------------------------------
Please reply in newsgroup
Use xdemechanik
<at>yahoo<dot>com for email
-------------------------------------------
Mark said:
I have a mailing list with name, address, city, state & zip with each item
in individual rows like a list of labels and a few empty rows of space
between each listing. How do I create/transfer this list into columns
accross so I can sort by city or zip? Thank you!
