Recognize values then populate up to 10 new rows

K

Kreiz

New Member
Joined
May 6, 2011
Messages
38
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,
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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
 
Upvote 0
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
ABCDEFGHIJK
1Avail*Truck*Origin-City*Or-St*Or-Zip*Dest-CityDe-StDe-ZipF/P*Ft.kLbs
25/7/2011RFORT COLLINSCO*OAKLANDCA*Full5344
35/7/2011RFORT COLLINSCO*GRANITE CITYIL*Full5344
45/7/2011RFORT COLLINSCO*SAN DIEGOCA*Full5344
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
ABCDEFGHIJK
1Avail*Truck*Origin-City*Or-St*Or-Zip*Dest-CityDe-StDe-ZipF/P*Ft.kLbs
25/7/2011RFORT COLLINSCO*OAKLANDCA*Full5344
35/7/2011RDENVERCO*OAKLANDCA*Full5344
45/7/2011RSIDNEYNE*OAKLANDCA*Full5344
55/7/2011RCOLBYKS*OAKLANDCA*Full5344
65/7/2011RPUEBLOCO*OAKLANDCA*Full5344
75/7/2011RCHEYENNEWY*OAKLANDCA*Full5344
85/7/2011RFORT COLLINSCO*FRESNOCA*Full5344
95/7/2011RFORT COLLINSCO*SAN FRANCISCOCA*Full5344
105/7/2011RFORT COLLINSCO*SACRAMENTOCA*Full5344
115/7/2011RFORT COLLINSCO*STOCKTONCA*Full5344
125/7/2011RFORT COLLINSCO*YUBA CITYCA*Full5344
135/7/2011RFORT COLLINSCO*GRANITE CITYIL*Full5344
145/7/2011RDENVERCO*GRANITE CITYIL*Full5344
155/7/2011RSIDNEYNE*GRANITE CITYIL*Full5344
165/7/2011RCOLBYKS*GRANITE CITYIL*Full5344
175/7/2011RPUEBLOCO*GRANITE CITYIL*Full5344
185/7/2011RCHEYENNEWY*GRANITE CITYIL*Full5344
195/7/2011RFORT COLLINSCO*ST LOUISMO*Full5344
205/7/2011RFORT COLLINSCO*CAPE GIRARDEAUMO*Full5344
215/7/2011RFORT COLLINSCO*QUINCYIL*Full5344
225/7/2011RFORT COLLINSCO*SPRINGFIELDIL*Full5344
235/7/2011RFORT COLLINSCO*EVANSVILLEIN*Full5344
245/7/2011RFORT COLLINSCO*KANSAS CITYMO*Full5344
255/7/2011RFORT COLLINSCO*SAN DIEGOCA*Full5344
265/7/2011RDENVERCO*SAN DIEGOCA*Full5344
275/7/2011RSIDNEYNE*SAN DIEGOCA*Full5344
285/7/2011RCOLBYKS*SAN DIEGOCA*Full5344
295/7/2011RPUEBLOCO*SAN DIEGOCA*Full5344
305/7/2011RCHEYENNEWY*SAN DIEGOCA*Full5344
315/7/2011RFORT COLLINSCO*IMPERIALCA*Full5344
325/7/2011RFORT COLLINSCO*OCEANSIDECA*Full5344
335/7/2011RFORT COLLINSCO*RIVERSIDECA*Full5344
345/7/2011RFORT COLLINSCO*YUMAAZ*Full5344
355/7/2011RFORT COLLINSCO*BLYTHECA*Full5344
Sheet2
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
Click to expand...

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.
 
Last edited:
Upvote 0
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
ABCDE
1*Origin-City*Or-St*Dest-CityDe-St
2FCL to OAK****
3*DENVERCOOAKLANDCA
4*SIDNEYNEOAKLANDCA
5*COLBYKSOAKLANDCA
6*PUEBLOCOOAKLANDCA
7*CHEYENNEWYOAKLANDCA
8*FORT COLLINSCOFRESNOCA
9*FORT COLLINSCOSAN FRANCISCOCA
10*FORT COLLINSCOSACRAMENTOCA
11*FORT COLLINSCOSTOCKTONCA
12*FORT COLLINSCOYUBA CITYCA
13FCL to GRNT****
14*DENVERCOGRANITE CITYIL
15*SIDNEYNEGRANITE CITYIL
16*COLBYKSGRANITE CITYIL
17*PUEBLOCOGRANITE CITYIL
18*CHEYENNEWYGRANITE CITYIL
19*FORT COLLINSCOST LOUISMO
20*FORT COLLINSCOCAPE GIRARDEAUMO
21*FORT COLLINSCOQUINCYIL
22*FORT COLLINSCOSPRINGFIELDIL
23*FORT COLLINSCOEVANSVILLEIN
24*FORT COLLINSCOKANSAS CITYMO
25FCL to SDIE****
26*DENVERCOSAN DIEGOCA
27*SIDNEYNESAN DIEGOCA
28*COLBYKSSAN DIEGOCA
29*PUEBLOCOSAN DIEGOCA
30*CHEYENNEWYSAN DIEGOCA
31*FORT COLLINSCOIMPERIALCA
32*FORT COLLINSCOOCEANSIDECA
33*FORT COLLINSCORIVERSIDECA
34*FORT COLLINSCOYUMAAZ
35*FORT COLLINSCOBLYTHECA
Sheet1
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Compare similar company names between two tables and return one of the values
Replies
2
Views
1K
realtoast
R
U
  • Question Question
When a value is detected in a cell, a VBA code or function will insert a new row with specific values and functions already in each cell
Replies
0
Views
347
ubergreen
U
U
  • Question Question
Create a new sheet in a separate workbook for each group of matching numbers/values
Replies
0
Views
247
ubergreen
U
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
399
Dan_W
D
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
88
Denny57
D

Forum statistics

Threads
1,224,542
Messages
6,179,421
Members
452,913
Latest member
JWD210

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top