Recognize values then populate up to 10 new rows

[Kreiz]

Greetings...

This site has done wonders for many of old friends who I've lost all touch with once they've found bigger paying jobs :D

I have a daily report of tracking information that i am needing to modify as rapid as possible. I need excel to recognize values in two columns and create 6-10 new columns in the same sheet or a new sheet with populated information.

eg..

*date*mile*beginCITY*beginSTATE*finalCITY*finalSTATE*size*time*who*what*

As the program recognizes a specific value for *beginCITY*beginSTATE* column as *Chicago*IL* I need it to create 3-5 new rows with all the same data but preset cities/states for that determined *Chicago*IL* and then do the same for *finalCITY*finalSTATE* 3-5 new rows only the city and state will change to the predetermined "outer cities/states"

Can anybody think of a way to start as i will be continuously be adding data for the city and state ranges (ie everytime it sees a Chicago IL it will make new row for 3-5 predetermined values i set)

Thank you for your time,

 

[hiker95]

Kreiz,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[Kreiz]

This is some daily original data, i will need a macro that recognizes the values in city/state columns and creates new rows based on date i have for each city/state combination..

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Avail*	Truck*	Origin-City*	Or-St*	Or-Zip*	Dest-City	De-St	De-Zip	F/P*	Ft.	kLbs
2	5/7/2011	R	FORT COLLINS	CO	*	OAKLAND	CA	*	Full	53	44
3	5/7/2011	R	FORT COLLINS	CO	*	GRANITE CITY	IL	*	Full	53	44
4	5/7/2011	R	FORT COLLINS	CO	*	SAN DIEGO	CA	*	Full	53	44
Sheet3

will become the below...The red data are what I am trying to get to auto populate..3-5 city/state combos for the Origin and Destination (I will up need to create a list i assume for each original city/state combo)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Avail*	Truck*	Origin-City*	Or-St*	Or-Zip*	Dest-City	De-St	De-Zip	F/P*	Ft.	kLbs
2	5/7/2011	R	FORT COLLINS	CO	*	OAKLAND	CA	*	Full	53	44
3	5/7/2011	R	DENVER	CO	*	OAKLAND	CA	*	Full	53	44
4	5/7/2011	R	SIDNEY	NE	*	OAKLAND	CA	*	Full	53	44
5	5/7/2011	R	COLBY	KS	*	OAKLAND	CA	*	Full	53	44
6	5/7/2011	R	PUEBLO	CO	*	OAKLAND	CA	*	Full	53	44
7	5/7/2011	R	CHEYENNE	WY	*	OAKLAND	CA	*	Full	53	44
8	5/7/2011	R	FORT COLLINS	CO	*	FRESNO	CA	*	Full	53	44
9	5/7/2011	R	FORT COLLINS	CO	*	SAN FRANCISCO	CA	*	Full	53	44
10	5/7/2011	R	FORT COLLINS	CO	*	SACRAMENTO	CA	*	Full	53	44
11	5/7/2011	R	FORT COLLINS	CO	*	STOCKTON	CA	*	Full	53	44
12	5/7/2011	R	FORT COLLINS	CO	*	YUBA CITY	CA	*	Full	53	44
13	5/7/2011	R	FORT COLLINS	CO	*	GRANITE CITY	IL	*	Full	53	44
14	5/7/2011	R	DENVER	CO	*	GRANITE CITY	IL	*	Full	53	44
15	5/7/2011	R	SIDNEY	NE	*	GRANITE CITY	IL	*	Full	53	44
16	5/7/2011	R	COLBY	KS	*	GRANITE CITY	IL	*	Full	53	44
17	5/7/2011	R	PUEBLO	CO	*	GRANITE CITY	IL	*	Full	53	44
18	5/7/2011	R	CHEYENNE	WY	*	GRANITE CITY	IL	*	Full	53	44
19	5/7/2011	R	FORT COLLINS	CO	*	ST LOUIS	MO	*	Full	53	44
20	5/7/2011	R	FORT COLLINS	CO	*	CAPE GIRARDEAU	MO	*	Full	53	44
21	5/7/2011	R	FORT COLLINS	CO	*	QUINCY	IL	*	Full	53	44
22	5/7/2011	R	FORT COLLINS	CO	*	SPRINGFIELD	IL	*	Full	53	44
23	5/7/2011	R	FORT COLLINS	CO	*	EVANSVILLE	IN	*	Full	53	44
24	5/7/2011	R	FORT COLLINS	CO	*	KANSAS CITY	MO	*	Full	53	44
25	5/7/2011	R	FORT COLLINS	CO	*	SAN DIEGO	CA	*	Full	53	44
26	5/7/2011	R	DENVER	CO	*	SAN DIEGO	CA	*	Full	53	44
27	5/7/2011	R	SIDNEY	NE	*	SAN DIEGO	CA	*	Full	53	44
28	5/7/2011	R	COLBY	KS	*	SAN DIEGO	CA	*	Full	53	44
29	5/7/2011	R	PUEBLO	CO	*	SAN DIEGO	CA	*	Full	53	44
30	5/7/2011	R	CHEYENNE	WY	*	SAN DIEGO	CA	*	Full	53	44
31	5/7/2011	R	FORT COLLINS	CO	*	IMPERIAL	CA	*	Full	53	44
32	5/7/2011	R	FORT COLLINS	CO	*	OCEANSIDE	CA	*	Full	53	44
33	5/7/2011	R	FORT COLLINS	CO	*	RIVERSIDE	CA	*	Full	53	44
34	5/7/2011	R	FORT COLLINS	CO	*	YUMA	AZ	*	Full	53	44
35	5/7/2011	R	FORT COLLINS	CO	*	BLYTHE	CA	*	Full	53	44
Sheet2

 

[hiker95]

Kreiz,

OK, we have the data in Sheet3.

1. What does Sheet2 look like before the data from Sheet3 has been copied to Sheet2?

2. What does Sheet2 look like after the data from Sheet3 has been copied to Sheet2?

 

[Kreiz]

Sheet 3 is imported values from the internet (no formulas involved). Sheet 2 completely typed in at the moment and this takes MUCH time. I go through each row in sheet 3 and create row by row in sheet 2 fake cities for the original origin and destination from scratch (copy & paste everything but the changing. city and state)...

1 row is 1 shipment lane. i need 1 row to look like 6-10 row's city and states changing automatically.

Ultimately dont know if I need more than 1 sheet to accomplish all this, i am only utilizing 2 because did not know of a more efficient way.

 

[hiker95]

Kreiz,

Sorry, I do not understand what you are trying to do.

Click on the Post Reply button, then just enter the word BUMP, and then click on the Submit Reply button, and someone else will assist you.

 

[Kreiz]

Bump,

I have dove into other realms of this board to find solutions to many other issues and tasks at work....Hope somebody can help me here.

This is an hourly report, and I would love some VBA help recognizing the 4 columns (origin and destination city/states) then running a routine for this pairing.

 

[hiker95]

Kreiz,

The red data are what I am trying to get to auto populate..3-5 city/state combos for the Origin and Destination (I will up need to create a list i assume for each original city/state combo)

Where is the RED data in worksheet Sheet2 coming from?

Is there another table/worksheet that contains this informaiton?

If so, can we have a screenshot?

And, what is the logic for picking the RED data?


What version of Excel are you using?


If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

 

[Kreiz]

ok I feel I may have confused some. The red data are the different origins & destinations we are trying to populate into the active report. I am needing a process to determine the Origin & Destination's City/state pairing (4 columns on 1 row) and then create 5-10 new rows with the pre-made database of correlating Origin & Destination's City/States.

ie. I have a shipment for Fort Collins CO to San Diego CA; I would like this 1 shipment to look like Denver CO to San Diego CA; Cheyenne WY to San Diego CA; etc with another 2-3 origins and 3-5 destinations from the pre-made lists. I've learned a bit about excel VBA and Access these past few months so am familiar with a bit more but this is still way over my head.

This is a sample of our "creative" sheet...meaning these are not real purchases we have at the moment, but we need these in the report for posting purposes. I probably will need to rearrange for this all to work properly but let me know, Ive only been flipping back and forth, copy and paste.

Excel Workbook
	A	B	C	D	E
1	*	Origin-City*	Or-St*	Dest-City	De-St
2	FCL to OAK	*	*	*	*
3	*	DENVER	CO	OAKLAND	CA
4	*	SIDNEY	NE	OAKLAND	CA
5	*	COLBY	KS	OAKLAND	CA
6	*	PUEBLO	CO	OAKLAND	CA
7	*	CHEYENNE	WY	OAKLAND	CA
8	*	FORT COLLINS	CO	FRESNO	CA
9	*	FORT COLLINS	CO	SAN FRANCISCO	CA
10	*	FORT COLLINS	CO	SACRAMENTO	CA
11	*	FORT COLLINS	CO	STOCKTON	CA
12	*	FORT COLLINS	CO	YUBA CITY	CA
13	FCL to GRNT	*	*	*	*
14	*	DENVER	CO	GRANITE CITY	IL
15	*	SIDNEY	NE	GRANITE CITY	IL
16	*	COLBY	KS	GRANITE CITY	IL
17	*	PUEBLO	CO	GRANITE CITY	IL
18	*	CHEYENNE	WY	GRANITE CITY	IL
19	*	FORT COLLINS	CO	ST LOUIS	MO
20	*	FORT COLLINS	CO	CAPE GIRARDEAU	MO
21	*	FORT COLLINS	CO	QUINCY	IL
22	*	FORT COLLINS	CO	SPRINGFIELD	IL
23	*	FORT COLLINS	CO	EVANSVILLE	IN
24	*	FORT COLLINS	CO	KANSAS CITY	MO
25	FCL to SDIE	*	*	*	*
26	*	DENVER	CO	SAN DIEGO	CA
27	*	SIDNEY	NE	SAN DIEGO	CA
28	*	COLBY	KS	SAN DIEGO	CA
29	*	PUEBLO	CO	SAN DIEGO	CA
30	*	CHEYENNE	WY	SAN DIEGO	CA
31	*	FORT COLLINS	CO	IMPERIAL	CA
32	*	FORT COLLINS	CO	OCEANSIDE	CA
33	*	FORT COLLINS	CO	RIVERSIDE	CA
34	*	FORT COLLINS	CO	YUMA	AZ
35	*	FORT COLLINS	CO	BLYTHE	CA
Sheet1

 

[hiker95]

Kreiz,

What version of Excel ar you using?