Creative Row Postings

[Kreiz]

I am in the shipping industry and often have shipments from not so popular places. I am looking for a way that excel will identify the status as "active", recognize the origin city/state and create 3-5 rows with other predetermined city/states that I have placed on another spreadsheet. On the below I have taken a regular pick location (Jacksonville) and have created 5 new rows under; copied the cities form the other spreadsheet that correspond to that location; then i will continue to copy all the other data down. This needs to be done for each shipment's Origin and Destination city.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
10	ACTIVE	7/28/2011	7/29/2011	1300	900	JACKSONVILLE	FL	MERIDIAN	MS	447	TL	44000	VR
11						ORLANDO	FL
12						SAVANNAH	GA
13						VALDOSTA	GA
14						RIDGELAND	SC
15						DAYTONA	FL
16	ACTIVE	7/28/2011	8/2/2011	700	630	JOLON	CA	LIMESTONE	ME	3247	PIECES	32400	FD
17	ACTIVE	7/28/2011	8/1/2011	1500	600	FORT COLLINS	CO	BISBEE	AZ	847	TL	43000	R
18	ON HOLD	7/28/2011	7/29/2011	1700	700	JACKSONVILLE	FL	CHARLESTON	SC	207	TL	44000	VR
19	BOOKED	7/27/2011	8/1/2011	800	700	PARAMOUNT	CA	LEE'S SUMMIT	MO	1576	PALLETS	24000	VR
20	BOOKED	7/27/2011	7/29/2011	1400	900	FORT COLLINS	CO	S SAN FRANCISCO	CA	1097	TL	44000	VR
21	BOOKED	7/27/2011	7/29/2011	1000	700	LAREDO	TX	DENVER	CO	997	PALLETS	44976	VR
report_2011-07-27_105009

 

[Kreiz]

This is how I have the Origin/Destination cities and states for our regular service locations

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	FORT COLLINS	CO	CARTERSVILLE	GA	JACKSONVILLE	FL	HOLLIS CENTER	ME	SALT LAKE CITY	UT	GREENWOOD	IN	RED BOILING SPRINGS	TN	LEE	FL	BRYAN	TX
2	DENVER	CO	HUNTSVILLE	AL	ORLANDO	FL	BANGO	ME	OGDEN	UT	INDIANAPOLIS	IN	KNOXVILLE	TN	MONTGOMERY	AL	SAN ANTONIO	TX
3	SIDNEY	NE	ATLANTA	GA	SAVANNAH	GA	HOPKINTON	NH	ROCK SPRINGS	WY	LOUISVILLE	KY	ATLANTA	GA	JACKSONVILLE	FL	HOUSTON	TX
4	COLBY	KS	ANDERSON	SC	VALDOSTA	GA	MONTPELIER	VT	TWIN FALLS	ID	DAYTON	OH	DECATUR	AL	TAMPA	FL	SHREVEPORT	LA
5	PUEBLO	CO	CHATTANOOGA	TN	RIDGELAND	SC	BOSTON	MA			EFFINGHAM	IL	NASHVILLE	TN	MACON	GA	DALLAS	TX
6	CHEYENNE	WY	BIRMINGHAM	AL	DAYTONA	FL	POLAND SPRING	ME					OWENSBORO	KY
Sheet1

 

[Kreiz]

Ultimately i would like to go from this one row (the Highlighted):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	STATUS	SHIP	DELIV	STIME	DTIME	SHIP CITY	S ST	CONS CITY	C ST	MILES	TYPE	WEIGHT
2	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	JACKSONVILLE	FL	817	TL	44000
Sheet1

To having all these other rows created from the city/state data on the other sheet:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	STATUS	SHIP	DELIV	STIME	DTIME	SHIP CITY	S ST	CONS CITY	C ST	MILES	TYPE	WEIGHT
2	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	JACKSONVILLE	FL	817	TL	44000
3	ACTIVE	7/28/2011	7/30/2011	1400	800	DENVER	CO	JACKSONVILLE	FL	817	TL	44000
4	ACTIVE	7/28/2011	7/30/2011	1400	800	SIDNEY	NE	JACKSONVILLE	FL	817	TL	44000
5	ACTIVE	7/28/2011	7/30/2011	1400	800	COLBY	KS	JACKSONVILLE	FL	817	TL	44000
6	ACTIVE	7/28/2011	7/30/2011	1400	800	PUEBLO	CO	JACKSONVILLE	FL	817	TL	44000
7	ACTIVE	7/28/2011	7/30/2011	1400	800	CHEYENNE	WY	JACKSONVILLE	FL	817	TL	44000
8	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	ORLANDO	FL	817	TL	44000
9	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	SAVANNAH	GA	817	TL	44000
10	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	VALDOSTA	GA	817	TL	44000
11	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	RIDGELAND	SC	817	TL	44000
12	ACTIVE	7/28/2011	7/30/2011	1400	800	FORT COLLINS	CO	DAYTONA	FL	817	TL	44000
Sheet1