Excel Address Lists

K

Karen

I have an address list in excel that I want to do a mail merge with. My
list, however, is not set up so that I have name in column 1, address in
column 2, and city state zip in column 3... It's set up like the following:

Row 1:Name
Row 2: Address
Row 3: City, State Zip

Is there an easy way to do a mass reformat to move the row 2 & 3 fields so
that they are column fields instead? I have about 200 addresses so manual
manipulation would be very inefficient.
 
G

Gord Dibben

Karen

If data is consistently in sets of three down the column(assume col A)

Select B1 and paste this in.

=INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1)

Copy across to D1

Copy B1:D1 down until zeros show up.

When happy, copy all and Paste Special>Values>OK>Esc.

Delete column A


Gord Dibben MS Excel MVP
 
K

Karen

Thanks! This worked like a charm!

Gord Dibben said:
Karen

If data is consistently in sets of three down the column(assume col A)

Select B1 and paste this in.

=INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1)

Copy across to D1

Copy B1:D1 down until zeros show up.

When happy, copy all and Paste Special>Values>OK>Esc.

Delete column A


Gord Dibben MS Excel MVP
Click to expand...
 
Top