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

Delete column A


Gord Dibben MS Excel MVP

On Wed, 23 Aug 2006 11:13:01 -0700, Karen
wrote:

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.

Thanks! This worked like a charm!

"Gord Dibben" wrote:

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

Delete column A


Gord Dibben MS Excel MVP

On Wed, 23 Aug 2006 11:13:01 -0700, Karen
wrote:

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.

Very good.

Thanks for the feedback.


Gord

On Wed, 23 Aug 2006 15:15:01 -0700, Karen
wrote:

Thanks! This worked like a charm!

"Gord Dibben" wrote:

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

Delete column A


Gord Dibben MS Excel MVP

On Wed, 23 Aug 2006 11:13:01 -0700, Karen
wrote:

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.



Gord Dibben MS Excel MVP