How to shift address listings from row list to columns?

[Mark]

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!

 

[Max]

If your data is nicely grouped,
with each group in 5 lines, viz:

name
add
city
state
zip

then an earlier suggestion given
which worked might be worth a try:

See: http://tinyurl.com/wgcb

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

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!

 

[Max]

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
-------------------------------------------

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!