Identify, copy range and consolidate information

[mblwood]

I have been working with Excel and VBA for 8 months and am having trouble with looking through data, copying rows and consolidating the rows.
I have a file for brush pick-up with two tabs, “week-1” and “pick-up”.
Week-1 has a list of data which is identified with a 1 thru 5 depending on which day of the week brush is picked up on. These numbers (1 thru 5) are random and have gaps.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
My objective is to look through column “E” on sheet “week-1” and find all the rows with a number 1 and copy the range “F” thru “O” from each row to the “pick-up” sheet beginning with “D13”
Than do the same thing for number 2 and paste starting with “D73” and so on.
<o> </o>
I have an example file and two jpegs but do not know how to include them.
<o> </o>
Thank you in advance for all your help.

 

[hiker95]

mblwood,

Welcome to the MrExcel forum.


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

 

[mblwood]

I am using Excel 2003

Week-1 sheet

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O
4			Day Picked-up	Work Order Number	Week-1				Month Year				Paid / Not Paid
5
6				Location				Estimate Cu. Yds
7
8				Address	Zone			Length	Width	Height	Cu.Yds.
9		#	4	1 Heritage Ct	1			6	5	5	5.56		Paid
10		#	1	2 Heritage Ct	1			5	5	5	4.63
11		#	1	3 Heritage Ct	1			2	4	1	0.30
12		#	1	4 Heritage Ct	1			3	3	5	1.67
13		#	1	5 Heritage Ct	1			4	2	2	0.59
14		#	2	1003 Chancellorsville Pkwy	10			5	1	3	0.56
15		#	2	321 Ne 33Rd St	4			6	5	4	4.44
16		#	2	5460 Brazoria Dr	9			4	5	6	4.44
17		#	2	3448 Daisy Ln	7			5	2	6	2.22
18		#	3	243 Enchanted Ct	2			9	3	3	3.00
19		#	3	859 Bentwater Pkwy	9			4	8	1	1.19
20		#	3	429 Martha St	4			3	4	2	0.89
21		#	3	87 W Mountain Creek Dr	8			7	7	4	7.26		Paid
22		#	4	2339 Calendar Ct	3			8	3	7	6.22		Paid
23		#	4	4529 Rugby Ln	10			6	2	4	1.78
24		#		649 W Springdale Ln	8			6	7	7	10.89		Not Paid
25		#	5	667 E Sandra Ln	10			7	4	6	6.22		Paid
26		#	5	867 Kitty Hawk Ln	6			3	2	5	1.11
27		#	5	367 Thoroughbred Ln	2			2	1	2	0.15
28		#		409 Martha St	4			7	7	4	7.26		Not Paid
29		#	5	147 Summit Ct	10			2	6	7	3.11
30		#
31		#
32		#
Week-1

Excel Workbook
	C	D	E	F	G	H	I	J	K	N	O	P
5		Day-1		Work Order Number
6
7		Date		Cu. Yds.				Tons	X
8
9		Location		Estimate Cu. Yds				Personnel			Equipment
10
11		Address	Zone	Length	Width	Height	Cu.Yds.	Employee	Hours		Equipment	Hours
12
13	1
14	2
15	3
16	4
17	5
18	6
19	7
20	8
21	9
22	10
23	11
24	12
25	13
26	14
27	15
28	16
29	17
30	18
Excel 2003 Pick-up sheet Pick-up

Excel 2003

 

[mblwood]

Loaded to Box.net

http://www.box.net/files#/files/0/f/83855458/Excel_Stuff

 

[hiker95]

mblwood,

Your link to Box.Net is requesting an ID and Password.

Please try again, and mark the workbook for sharing.

 

[mblwood]

Please try this link

http://www.box.net/shared/8e58rcm1gf

 

[hiker95]

mblwood,


If we start with just this section:

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O
4	Day Picked-up	Work Order Number	Week-1				Month Year				Paid /Not Paid
5
6		Location				Estimate Cu. Yds
7
8		Address	Zone			Length	Width	Height	Cu.Yds.
9	4	1 Heritage Ct	1			6	5	5	5.56		Paid
10	1	2 Heritage Ct	1			5	5	5	4.63
11	1	3 Heritage Ct	1			2	4	1	0.30
12	1	4 Heritage Ct	1			3	3	5	1.67
13	1	5 Heritage Ct	1			4	2	2	0.59
Week-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M9	1. / Cell Value greater than 5	Abc
O9	1. / Cell Value equal to ="Paid"	Abc
O9	2. / Cell Value equal to ="Not Paid"	Abc
M10	1. / Cell Value greater than 5	Abc
O10	1. / Cell Value equal to ="Paid"	Abc
O10	2. / Cell Value equal to ="Not Paid"	Abc
M11	1. / Cell Value greater than 5	Abc
O11	1. / Cell Value equal to ="Paid"	Abc
O11	2. / Cell Value equal to ="Not Paid"	Abc
M12	1. / Cell Value greater than 5	Abc
O12	1. / Cell Value equal to ="Paid"	Abc
O12	2. / Cell Value equal to ="Not Paid"	Abc
M13	1. / Cell Value greater than 5	Abc
O13	1. / Cell Value equal to ="Paid"	Abc
O13	2. / Cell Value equal to ="Not Paid"	Abc

Can I get a screenshot of the next worksheet, with just the 1's from above, filled in?

Excel Workbook
	C	D	E	F	G	H	I	J	K	N	O	P
5		Day-1		Work Order Number
6
7		Date		Cu. Yds.				Tons	X
8
9		Location		Estimate Cu. Yds				Personnel					Equipment
10
11		Address	Zone	Length	Width	Height	Cu.Yds.	Employee	Hours		Equipment	Hours
12
13	1
14	2
15	3
16	4
17	5
Pick-up

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I13	1. / Cell Value greater than 5	Abc
I14	1. / Cell Value greater than 5	Abc
I15	1. / Cell Value greater than 5	Abc
I16	1. / Cell Value greater than 5	Abc
I17	1. / Cell Value greater than 5	Abc

 

[hiker95]

mblwood,

Also, just to see where stuff goes, in the same screenshot of worksheet Pick-up, also include the 4 in row 9 from worksheet Week-1

 

[mblwood]

This is the first couple of days.
I have shaded the areas that have permanent information (Columns C,E,K,N,O,S,T) leaving the areas open for information being transferred. (Columns D,H,I&J going down 50 rows for each day)
Also, please keep in mind that each week will have different addresses and the typical day will have 20 to 30 pick-ups.

I will also upload a newer file to Box.net
Thanks again hiker95

Excel Workbook
	B	C	D	E	H	I	J	K	N	O	S	T	W
4
5			Day-1		Work Order Number
7			Date		Cu. Yds.			7.19	Tons	X
9			Location		Estimate Cu. Yds				Personnel		Equipment
11			Address	Zone	Length	Width	Height	Cu.Yds.	Employee	Hours	Equipment	Hours
13		1	2 Heritage Ct	1	5	5	5	4.63	Ceniceros Jose	8.00	2518	8.00
14		2	3 Heritage Ct	1	2	4	1	0.30	Scott James	8.00	2241	8.00
15		3	4 Heritage Ct	1	3	3	5	1.67	Velasquez Cristoval	8.00	2242
16		4	5 Heritage Ct	1	4	2	2	0.59			2243
17		5									2544	8.00
62		50
65			Day-2		Work Order Number
67			Date		Cu. Yds.			11.67	Tons	X
69			Location		Estimate Cu. Yds				Personnel		Equipment
71			Address	Zone	Length	Width	Height	Cu.Yds.	Employee	Hours	Equipment	Hours
73		1	1003 Chancellorsville Pkwy	10	5	1	3	0.56	Ceniceros Jose	8.00	2518	8.00
74		2	321 Ne 33Rd St	4	6	5	4	4.44	Scott James	8.00	2241	8.00
75		3	5460 Brazoria Dr	9	4	5	6	4.44	Velasquez Cristoval	8.00	2242
76		4	3448 Daisy Ln	7	5	2	6	2.22			2243
77		5									2544	8.00
122		50
125			Day-3		Work Order Number
126
127			Date		Cu. Yds.			12.33	Tons	X
128
Pick-up

Excel 2003

 

[hiker95]

mblwood,

Both Box.Net links do not work.

Please supply another workbook, with worksheets Week-1 with sample data, and worksheet Pick-up manually completed.

This way I will be able to see where data is being moved from in worksheetWeek-1, to where it is ending up in worksheet Pick-up.