have downloaded store list consisting of the following format below.

this is succession of rows, would like to move to columns to eventually run
in MS Word as data for a mail merge.

"Each record" would start with Store.,such that macro would copy Store to
say Col 2, then XYZ at Palm Springs... to Col2+1 and so forth until it reachs
the next line with Store and would go back to Col2
hope this clear
thx

Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985

Are the data sets consistent as in 6 cells per set?

If so, enter this in G1 and copy across to L1.

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

Select G1:L1 and copy down until you get zeros showing up.

Paste SpecialValuesOKEsc then delete column A

If you want a macro post back.


Gord Dibben MS Excel MVP

On Mon, 10 Mar 2008 11:28:03 -0700, reno wrote:

have downloaded store list consisting of the following format below.

this is succession of rows, would like to move to columns to eventually run
in MS Word as data for a mail merge.

"Each record" would start with Store.,such that macro would copy Store to
say Col 2, then XYZ at Palm Springs... to Col2+1 and so forth until it reachs
the next line with Store and would go back to Col2
hope this clear
thx

Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985

there are 5 to 7 per set.

if the "trigger" were the word "sales" that would handle it.

would apprecaite macro

"Gord Dibben" wrote:

Are the data sets consistent as in 6 cells per set?

If so, enter this in G1 and copy across to L1.

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

Select G1:L1 and copy down until you get zeros showing up.

Paste SpecialValuesOKEsc then delete column A

If you want a macro post back.


Gord Dibben MS Excel MVP

On Mon, 10 Mar 2008 11:28:03 -0700, reno wrote:

have downloaded store list consisting of the following format below.

this is succession of rows, would like to move to columns to eventually run
in MS Word as data for a mail merge.

"Each record" would start with Store.,such that macro would copy Store to
say Col 2, then XYZ at Palm Springs... to Col2+1 and so forth until it reachs
the next line with Store and would go back to Col2
hope this clear
thx

Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985

Where would the word "sales" be located?

I don't see one in your example data.

Does each set have a blank row between?

I won't be back on line for 5 or 6 days so hopefully you will get something
before then.


Gord

On Mon, 10 Mar 2008 18:29:00 -0700, reno wrote:

there are 5 to 7 per set.

if the "trigger" were the word "sales" that would handle it.

would apprecaite macro

"Gord Dibben" wrote:

Are the data sets consistent as in 6 cells per set?

If so, enter this in G1 and copy across to L1.

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

Select G1:L1 and copy down until you get zeros showing up.

Paste SpecialValuesOKEsc then delete column A

If you want a macro post back.


Gord Dibben MS Excel MVP

On Mon, 10 Mar 2008 11:28:03 -0700, reno wrote:

have downloaded store list consisting of the following format below.

this is succession of rows, would like to move to columns to eventually run
in MS Word as data for a mail merge.

"Each record" would start with Store.,such that macro would copy Store to
say Col 2, then XYZ at Palm Springs... to Col2+1 and so forth until it reachs
the next line with Store and would go back to Col2
hope this clear
thx

Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985

in my example the first line is store as shown below and then each succeeding
"records" starts with word Store on first line similar to this (as copied
from below. There are 5 to 7 rows in each "record".

thanks for any help
Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985
Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985
Liqour: (407)-260-7747


"Gord Dibben" wrote:

Where would the word "sales" be located?

I don't see one in your example data.

Does each set have a blank row between?

I won't be back on line for 5 or 6 days so hopefully you will get something
before then.


Gord

On Mon, 10 Mar 2008 18:29:00 -0700, reno wrote:

there are 5 to 7 per set.

if the "trigger" were the word "sales" that would handle it.

would apprecaite macro

"Gord Dibben" wrote:

Are the data sets consistent as in 6 cells per set?

If so, enter this in G1 and copy across to L1.

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

Select G1:L1 and copy down until you get zeros showing up.

Paste SpecialValuesOKEsc then delete column A

If you want a macro post back.


Gord Dibben MS Excel MVP

On Mon, 10 Mar 2008 11:28:03 -0700, reno wrote:

have downloaded store list consisting of the following format below.

this is succession of rows, would like to move to columns to eventually run
in MS Word as data for a mail merge.

"Each record" would start with Store.,such that macro would copy Store to
say Col 2, then XYZ at Palm Springs... to Col2+1 and so forth until it reachs
the next line with Store and would go back to Col2
hope this clear
thx

Store
XYZ at Palm Springs Crossing (Store #00690)
482 E Altamonte Dr Ste 1005
Altamonte Springs, FL 32701-4604
Main: (407) 260-9973
Pharmacy: (407) 260-9985