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,

 

[Kreiz]

I have excel 2007 at home, 2010 at work.

 

[hiker95]

Kreiz,

I assume that the codes in Sheet1 in column A are a standard location code for you industry. Is that so?

You will have to build a table in Sheet1 similar to this:

Excel Workbook
	A	B	C	D	E	F	G	H
1		Origin-City	Or-St	Dest-City	De-St		FCL to OAK	FORT COLLINS CO|OAKLAND CA
2	FCL to OAK						FCL to GRNT	FORT COLLINS CO|GRANITE CITY IL
3		DENVER	CO	OAKLAND	CA		FCL to SDIE	FORT COLLINS CO|SAN DIEGO 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
36
Sheet1

The above information in columns G and H will make it so that we can search for information and insert that data in columns B thru E into Sheet2.

We first copy the raw downloaded data from Sheet3 into Sheet 2, and add column L:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
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	FORT COLLINS CO|OAKLAND CA
3	5/7/2011	R	FORT COLLINS	CO		GRANITE CITY	IL		Full	53	44	FORT COLLINS CO|GRANITE CITY IL
4	5/7/2011	R	FORT COLLINS	CO		SAN DIEGO	CA		Full	53	44	FORT COLLINS CO|SAN DIEGO CA
5
Sheet2

I can now seatch column L for a match in Sheet1 column G to find the group in column A, and insert rows in Sheet2, and copy the group data from Sheet1 columns B thru E.

Does this sound feasable?









Right now there is no correlation between the data table in worksheet Sheet1

 

[Kreiz]

Somewhat, the matching and searching setup i believe I understand.

I am trying to add rows not columns. As sheet 2 is the raw data downloaded, I am looking to make those 3 orders (sheet2 row2:3 A:K) turn into many many more. The kicker is that I have 50-80 different orders(rows) of raw data so you add 5-10 orders(rows) we will have 250-800 orders creatively made.

 

[hiker95]

Kreiz,

I assume that the codes in Sheet1 in column A are a standard location code for you industry. Is that so?

As sheet 2 is the raw data downloaded

OK, you do not need a Sheet3.


Right now there is no correlation between the data table in worksheet Sheet1 and the raw data downloaded in Sheet2.

I am trying to explain that I will need another table in Sheet1 columns G and K, so that I can find the correct groups in column A.

 

[hiker95]

Kreiz,

Without the additional table in Sheet1 columns G and H, I can not do what you are asking.

Nowhere in your workbook is there a way to search for, or understand that:
FCL to OAK

stands for:

Excel Workbook
	C	D	E	F	G
2	FORT COLLINS	CO		OAKLAND	CA
Sheet2

 

[Kreiz]

The FCL to OAK is only for my reference, I believe I will have to create a different database. Not sure though on how to start that with out knowing how excel is going to relate or pair 4 columns once it realizes it is one in the database of creative lanes.

 

[hiker95]

Kreiz,

You have not setteled on what worksheet Sheet1 will look like, so, what follows is a workable solution.

I have changed what column A on worksheet Sheet1 looks like:


Sample worksheets before the macro:

Excel Workbook
	A	B	C	D	E
1		Origin-City	Or-St	Dest-City	De-St
2	FORT COLLINS CO | OAKLAND CA
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	FORT COLLINS CO | GRANITE CITY IL
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	FORT COLLINS CO | SAN DIEGO CA
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
36
Sheet1

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
5
Sheet2

After the macro on worksheet Sheet2:

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
36
Sheet2

If we change the number of rows for the groups on worksheet Sheet1:

Excel Workbook
	A	B	C	D	E
1		Origin-City	Or-St	Dest-City	De-St
2	FORT COLLINS CO | OAKLAND CA
3		DENVER	CO	OAKLAND	CA
4		SIDNEY	NE	OAKLAND	CA
5		COLBY	KS	OAKLAND	CA
6		PUEBLO	CO	OAKLAND	CA
7	FORT COLLINS CO | GRANITE CITY IL
8		DENVER	CO	GRANITE CITY	IL
9		SIDNEY	NE	GRANITE CITY	IL
10		COLBY	KS	GRANITE CITY	IL
11		PUEBLO	CO	GRANITE CITY	IL
12		CHEYENNE	WY	GRANITE CITY	IL
13		FORT COLLINS	CO	ST LOUIS	MO
14		FORT COLLINS	CO	CAPE GIRARDEAU	MO
15	FORT COLLINS CO | SAN DIEGO CA
16		DENVER	CO	SAN DIEGO	CA
17		SIDNEY	NE	SAN DIEGO	CA
18		COLBY	KS	SAN DIEGO	CA
19
Sheet1

Worksheet Sheet2 before the macro:

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
5
Sheet2

After the macro on worksheet Sheet2:

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	FORT COLLINS	CO		GRANITE CITY	IL		Full	53	44
8	5/7/2011	R	DENVER	CO		GRANITE CITY	IL		Full	53	44
9	5/7/2011	R	SIDNEY	NE		GRANITE CITY	IL		Full	53	44
10	5/7/2011	R	COLBY	KS		GRANITE CITY	IL		Full	53	44
11	5/7/2011	R	PUEBLO	CO		GRANITE CITY	IL		Full	53	44
12	5/7/2011	R	CHEYENNE	WY		GRANITE CITY	IL		Full	53	44
13	5/7/2011	R	FORT COLLINS	CO		ST LOUIS	MO		Full	53	44
14	5/7/2011	R	FORT COLLINS	CO		CAPE GIRARDEAU	MO		Full	53	44
15	5/7/2011	R	FORT COLLINS	CO		SAN DIEGO	CA		Full	53	44
16	5/7/2011	R	DENVER	CO		SAN DIEGO	CA		Full	53	44
17	5/7/2011	R	SIDNEY	NE		SAN DIEGO	CA		Full	53	44
18	5/7/2011	R	COLBY	KS		SAN DIEGO	CA		Full	53	44
19
Sheet2

The BOLD and RED text is used to just show how the results would look.



Is the above acceptable to you? Would you like to try the macro after you make the changes to column A on worksheet Sheet1?

 

[Kreiz]

This is exactly what I have been looking for. Worksheet 1 will be populated with all of the many differant lanes we service on a regular basis and I love how you were able to remove rows and the macro still adjusts and only places that many. How does it work!?! and what is the macro.

 

[hiker95]

Kreiz,


I was trying to find a way that I could search Sheet2 data against Sheet1 information.

Column L displays a formula that creates the search information:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
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	FORT COLLINS CO | OAKLAND CA
3	5/7/2011	R	FORT COLLINS	CO		GRANITE CITY	IL		Full	53	44	FORT COLLINS CO | GRANITE CITY IL
4	5/7/2011	R	FORT COLLINS	CO		SAN DIEGO	CA		Full	53	44	FORT COLLINS CO | SAN DIEGO CA
5
Sheet2

So I then changed what was on Sheet1, column A, from this:

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
36
Sheet1

To this:

Excel Workbook
	A	B	C	D	E
1		Origin-City	Or-St	Dest-City	De-St
2	FORT COLLINS CO | OAKLAND CA
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	FORT COLLINS CO | GRANITE CITY IL
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	FORT COLLINS CO | SAN DIEGO CA
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
36
Sheet1

 

[hiker95]

Kreiz,


You will have to manually change all the information in Sheet1, column A, to what I have done (information and format).


After you do the above, then you can test your new route information in Sheet1 against Sheet2.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 08/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=548474
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, FR As Long, SR As Long, ER As Long
Application.ScreenUpdating = True
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
w2.Columns(12).ClearContents
LR = w2.Cells(Rows.Count, 1).End(xlUp).Row
With w2.Range("L2:L" & LR)
  .FormulaR1C1 = "=RC[-9]&"" ""&RC[-8]&"" | ""&RC[-6]& "" ""&RC[-5]"
  .Value = .Value
End With
For a = LR To 2 Step -1
  FR = 0
  On Error Resume Next
  FR = Application.Match(w2.Range("L" & a), w1.Columns(1), 0)
  On Error GoTo 0
  If FR <> 0 Then
    SR = FR + 1
    ER = w1.Range("B" & SR).End(xlDown).Row
    w2.Rows(a).Offset(1).Resize(ER - SR + 1).Insert
    w2.Rows(a).Copy w2.Rows(a).Offset(1).Resize(ER - SR + 1)
    Application.DisplayAlerts = False
    w1.Range("B" & SR & ":C" & ER).Copy w2.Range("C" & a + 1).Resize(ER - SR, 2)
    Application.DisplayAlerts = False
    w1.Range("D" & SR & ":E" & ER).Copy w2.Range("F" & a + 1).Resize(ER - SR, 2)
  End If
Next a
w2.Columns(12).ClearContents
w2.Activate
Application.ScreenUpdating = False
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.