Combine & match 2 Worksheets

[thewiseguy]

Hi all,

I am trying to create a VBA code which will look at my purchase order info (PO Tab) and my bills info (bill tab) and combine them in a 3rd worksheet. I'm not sure what additional information you might need but this is how I would like it to look in an ideal manner. I'm open to some variation if it gets too complex: in the following manner:

project-profit.xlsx
	A	B											M	N
1	Purchase Order Number	Vendor Name											Total	Adjustment
2	PO-01793	Halco Lighting Technologies											$17,809.00	$0.00
3	PO-01794	Maxlite, Inc.											$1,802.00	$0.00
4	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00
5	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00
6	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00
7	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00
8	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00
9	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00
10	PO-01797	Veolia ES Technical Solutions, L.L.C											$120.60	$0.00
11	PO-01798	United Rentals											$1,729.46	$107.46
12	PO-01798	United Rentals											$1,729.46	$107.46
13	PO-01798	United Rentals											$1,729.46	$107.46
14	PO-01798	United Rentals											$1,729.46	$107.46
15	PO-01799	Louis T. Roselle Inc.											$625.00	$0.00
16	PO-01805	Feldman Brothers Electrical Supply Co.											$205.76	$0.00
17	PO-01805	Feldman Brothers Electrical Supply Co.											$205.76	$0.00
18	PO-01806	Feldman Brothers Electrical Supply Co.											$391.81	$0.00
19	PO-01807	Maxlite, Inc.											$7,629.25	$0.00
20	PO-01807	Maxlite, Inc.											$7,629.25	$0.00
21	PO-01807-1	Maxlite, Inc.											$7,520.00	$0.00
22	PO-01811	PB Lighting											$378.00	$0.00
23	PO-01811	PB Lighting											$378.00	$0.00
24	PO-01815	Maxlite, Inc.											$791.75	$0.00
25	PO-01817	Feldman Brothers Electrical Supply Co.											$233.40	$0.00
26	PO-01821	Louis T. Roselle Inc.											$410.68	$0.00
PO

project-profit.xlsx
	A	B										L	M
1	Purchase Order Number	Vendor Name										Total	Adjustment
2	PO-01780	Halco Lighting Technologies										$738.51	$0.00
3	PO-01793	Halco Lighting Technologies										$17,809.00	$0.00
4	PO-01794	Maxlite, Inc.										$933.89	$0.00
5	PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
6	PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
7	PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
8	PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
9	PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
10	PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
11	PO-01798	United Rentals										$1,729.46	$107.46
12	PO-01798	United Rentals										$1,729.46	$107.46
13	PO-01798	United Rentals										$1,729.46	$107.46
14	PO-01798	United Rentals										$1,729.46	$107.46
15	PO-01799	Louis T. Roselle Inc.										$753.11	$0.00
16	PO-01805	Feldman Brothers Electrical Supply Co.										$205.76	$0.00
17	PO-01805	Feldman Brothers Electrical Supply Co.										$205.76	$0.00
18	PO-01806	Feldman Brothers Electrical Supply Co.										$391.81	$0.00
19	PO-01807	Maxlite, Inc.										$142.00	$0.00
20	PO-01807	Maxlite, Inc.										$437.25	$0.00
21	PO-01807-1	Maxlite, Inc.										$7,520.00	$0.00
22	PO-01815	Maxlite, Inc.										$75.84	$0.00
23	PO-01817	Feldman Brothers Electrical Supply Co.										$233.40	$0.00
24	PO-01821	Louis T. Roselle Inc.										$410.68	$0.00
25		American Express										$7,910.32	$0.00
26		Campbell Electric LLC										$12,280.00	$0.00
27		Campbell Electric LLC										$12,280.00	$0.00
28		EF Lighting Improvement LLC										$4,400.00	$0.00
29		EF Lighting Improvement LLC										$4,400.00	$0.00
30		EF Lighting Improvement LLC										$4,400.00	$0.00
31		EF Lighting Improvement LLC										$4,400.00	$0.00
32		Maxlite, Inc.										$933.89	$0.00
33		Maxlite, Inc.										$75.84	$0.00
Bill

project-profit.xlsx
	A	B											M	N	O	P	Q										AA	AB
1	Purchase Order Number	Vendor Name											Total	Adjustment		Purchase Order Number	Vendor Name										Total	Adjustment
2
3																PO-01780	Halco Lighting Technologies										$738.51	$0.00
4	Subtotal PO-01780												0	0		Subtotal PO-01780											$738.51	$0.00
5
6	PO-01793	Halco Lighting Technologies											$17,809.00	$0.00		PO-01793	Halco Lighting Technologies										$17,809.00	$0.00
7	Subtotal PO-01793												$17,809.00	$0.00		Subtotal PO-01793											$17,809.00	$0.00
8
9	PO-01794	Maxlite, Inc.											$1,802.00	$0.00		PO-01794	Maxlite, Inc.										$933.89	$0.00
10	Subtotal PO-01794												$1,802.00	$0.00		Subtotal PO-01794											$933.89	$0.00
11
12	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00		PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
13	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00		PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
14	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00		PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
15	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00		PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
16	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00		PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
17	PO-01796	Feldman Brothers Electrical Supply Co.											$3,720.82	$0.00		PO-01796	Feldman Brothers Electrical Supply Co.										$3,720.82	$0.00
18	Subtotal PO-01796												$22,324.92	$0.00		Subtotal PO-01796											$22,324.92	$0.00
19
20	PO-01797	Veolia ES Technical Solutions, L.L.C											$120.60	$0.00
21	Subtotal PO-01797												$120.60	$0.00		Subtotal PO-01797											0	0
22
23	PO-01798	United Rentals											$1,729.46	$107.46		PO-01798	United Rentals										$1,729.46	$107.46
24	PO-01798	United Rentals											$1,729.46	$107.46		PO-01798	United Rentals										$1,729.46	$107.46
25	PO-01798	United Rentals											$1,729.46	$107.46		PO-01798	United Rentals										$1,729.46	$107.46
26	PO-01798	United Rentals											$1,729.46	$107.46		PO-01798	United Rentals										$1,729.46	$107.46
27	Subtotal PO-01798												$6,917.84	$429.84		Subtotal PO-01798											$6,917.84	$429.84
28
29	PO-01799	Louis T. Roselle Inc.											$625.00	$0.00		PO-01799	Louis T. Roselle Inc.										$753.11	$0.00
30	Subtotal PO-01799												$625.00	$0.00		Subtotal PO-01799											$753.11	$0.00
31
32	PO-01805	Feldman Brothers Electrical Supply Co.											$205.76	$0.00		PO-01805	Feldman Brothers Electrical Supply Co.										$205.76	$0.00
33	PO-01805	Feldman Brothers Electrical Supply Co.											$205.76	$0.00		PO-01805	Feldman Brothers Electrical Supply Co.										$205.76	$0.00
34	Subtotal PO-01805												$411.52	$0.00		Subtotal PO-01805											$411.52	$0.00
35
36	PO-01806	Feldman Brothers Electrical Supply Co.											$391.81	$0.00		PO-01806	Feldman Brothers Electrical Supply Co.										$391.81	$0.00
37	Subtotal PO-01806												$391.81	$0.00		Subtotal PO-01806											$391.81	$0.00
38
39	PO-01807	Maxlite, Inc.											$7,629.25	$0.00		PO-01807	Maxlite, Inc.										$142.00	$0.00
40	PO-01807	Maxlite, Inc.											$7,629.25	$0.00		PO-01807	Maxlite, Inc.										$437.25	$0.00
41	Subtotal PO-01807												$15,258.50	$0.00		Subtotal PO-01807											$15,258.50	$0.00
42
43	PO-01807-1	Maxlite, Inc.											$7,520.00	$0.00		PO-01807-1	Maxlite, Inc.										$7,520.00	$0.00
44	Subtotal PO-01807-1												$7,520.00	$0.00		Subtotal PO-01807-1											$7,520.00	$0.00
45
46	PO-01811	PB Lighting											$378.00	$0.00
47	PO-01811	PB Lighting											$378.00	$0.00
48	Subtotal PO-01811												$756.00	$0.00		Subtotal PO-01811											0	0
49
50	PO-01815	Maxlite, Inc.											$791.75	$0.00		PO-01815	Maxlite, Inc.										$75.84	$0.00
51	Subtotal PO-01815												$791.75	$0.00		Subtotal PO-01815											$75.84	$0.00
52
53	PO-01817	Feldman Brothers Electrical Supply Co.											$233.40	$0.00		PO-01817	Feldman Brothers Electrical Supply Co.										$233.40	$0.00
54	Subtotal PO-01817												$233.40	$0.00		Subtotal PO-01817											$233.40	$0.00
55
56	PO-01821	Louis T. Roselle Inc.											$410.68	$0.00		PO-01821	Louis T. Roselle Inc.										$410.68	$0.00
57	Subtotal PO-01821												$410.68	$0.00		Subtotal PO-01821											410.68	0
58
59																	American Express										$7,910.32	$0.00
60																	Campbell Electric LLC										$12,280.00	$0.00
61																	Campbell Electric LLC										$12,280.00	$0.00
62																	EF Lighting Improvement LLC										$4,400.00	$0.00
63																	EF Lighting Improvement LLC										$4,400.00	$0.00
64																	EF Lighting Improvement LLC										$4,400.00	$0.00
65																	EF Lighting Improvement LLC										$4,400.00	$0.00
66																	Maxlite, Inc.										$933.89	$0.00
67																	Maxlite, Inc.										$75.84	$0.00
combine

Cell Formulas
Range	Formula
M4:N4,AA57:AB57,M57:N57,AA54:AB54,M54:N54,AA51:AB51,M51:N51,M44:N44,AA37:AB37,M37:N37,AA30:AB30,M30:N30,AA21:AB21,M21:N21,AA10:AB10,M10:N10,AA7:AB7,M7:N7,AA4:AB4	M4	=SUM(M3)
M18:N18,AA18:AB18	M18	=SUM(M12:M17)
M27:N27,AA27:AB27	M27	=SUM(M23:M26)
M34:N34,AA48:AB48,M48:N48,M41:N41,AA34:AB34	M34	=SUM(M32:M33)

 

[thewiseguy]

bumping up.....

 

[mohsinbipu]

Better if you use Power Query as This is excel build-in feature and the speed will be many times higher than VBA.

VBA files may not support MAC.

 

[9davide998]

Hi Thewiseguy, I saw the job offer on Upwork, but I couldn't apply, I created the code in VBA you needed.

 

[Fluff]

@9davide998 please post your code to the board for all to see, as per Rule#4