I have a list of names and addresses that are in rows. Does anyone know of
way to move the address and city state zip to columns so that I can do a mail
merge and print labels. I know I could cut & paste but that would take a
long time.

Example
Name1
Address 1
City state zip1
Name2
Address 2
City state zip2

if name1 is in a2
I would like for address 1 to go to B2 and city state to go to c2

Are these data consistent?

i.e. 3 rows per address

If so, enter this in B2 and copy across to D2 then select B2:D2 and drag/copy
down until you get zeros.

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

When happy, select columns B:D and copypaste specialvaluesokesc.

Delete Column A


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 18:01:02 -0800, DOwens
wrote:

I have a list of names and addresses that are in rows. Does anyone know of
way to move the address and city state zip to columns so that I can do a mail
merge and print labels. I know I could cut & paste but that would take a
long time.

Example
Name1
Address 1
City state zip1
Name2
Address 2
City state zip2

if name1 is in a2
I would like for address 1 to go to B2 and city state to go to c2

THANKS GORD - that worked perfectly, just what I needed. THANKS SO MUCH!!
Dianne

"Gord Dibben" wrote:

Are these data consistent?

i.e. 3 rows per address

If so, enter this in B2 and copy across to D2 then select B2:D2 and drag/copy
down until you get zeros.

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

When happy, select columns B:D and copypaste specialvaluesokesc.

Delete Column A


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 18:01:02 -0800, DOwens
wrote:

I have a list of names and addresses that are in rows. Does anyone know of
way to move the address and city state zip to columns so that I can do a mail
merge and print labels. I know I could cut & paste but that would take a
long time.

Example
Name1
Address 1
City state zip1
Name2
Address 2
City state zip2

if name1 is in a2
I would like for address 1 to go to B2 and city state to go to c2

Good to hear.

Thanks for the feedback.

Gord

On Wed, 28 Nov 2007 04:59:01 -0800, DOwens
wrote:

THANKS GORD - that worked perfectly, just what I needed. THANKS SO MUCH!!
Dianne

"Gord Dibben" wrote:

Are these data consistent?

i.e. 3 rows per address

If so, enter this in B2 and copy across to D2 then select B2:D2 and drag/copy
down until you get zeros.

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

When happy, select columns B:D and copypaste specialvaluesokesc.

Delete Column A


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 18:01:02 -0800, DOwens
wrote:

I have a list of names and addresses that are in rows. Does anyone know of
way to move the address and city state zip to columns so that I can do a mail
merge and print labels. I know I could cut & paste but that would take a
long time.

Example
Name1
Address 1
City state zip1
Name2
Address 2
City state zip2

if name1 is in a2
I would like for address 1 to go to B2 and city state to go to c2