Excel Address Lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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...
 
Back
Top