long address list, name-address-city, listed vertically, how do y.

K

kb

I need to sort a long address list, that is listed down not accross.
name-address-city, is that possible
 
G

Gord Dibben

kb

Is your data consistent?

i.e. sets of 3 or 4 or 5? Like so....

Name
Address
City
State
Zip
Name
Address
City
State
Zip

If so, there are a couple of methods, one using formulas, the other using a
macro.

Assume your data starts in A2.

In B2 enter this formula and drag/copy across to F2 and down until you get
zeros appearing.

=OFFSET($A$1,(ROW()-1)*5+COLUMN()-1,0)

The 5 should be adjusted to whatever number constitutes a set.


Gord Dibben Excel MVP
 
D

David McRitchie

Some additional information you might need.

Gord's assumption is that since the list is in one column that
you would want each person (address) on there own row,
which is what Gord gave you, because that is the way you
would keep such data in a spreadsheet; otherwise, there wouldn't
be any purpose in using a spreadsheet. Before you sort you would
want to convert those formulas to constants.

Select all (ctrl+a)
Edit, Paste Special, Values

You would use mail merge in MS Word to print labels from
your Excel spreadsheet.

If your data does not match Gord's assumption some other
possibilities -- mainly that you have a blank row between sets
can be found in http://www.mvps.org/dmcritchie/excel/snakecol.htm

For information on using Mail Merge, with your name, address portions
all in separate columns see
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
 
Top