VBA To Merge 2 Excel Sheets

[madkinson]

I have a template file that is used to load data into a 3rd party application.

White_Eagle_Rail_Car_Data (4).xlsx
	E	F	G	H	I	J	K	L	M	N	O
1	Location	Date	Train #	Ref #	Position	Vehicle ID	Gross	Net	Qty	Load Status	In/Out Status
2	109		1				0	0		E	I
3	109		1				0	0		E	I
4	109		1				0	0		E	I
5	109		1				0	0		E	I
6	109		1				0	0		E	I
Dataset 1

Cell Formulas
Range	Formula
K2:K6	K2	=M2*2000

I also have another file created in a 3rd application.

White_Eagle_Rail_Car_Data (4).xlsx
	A	B	C
1	Date	Ref #	Vehicle ID
2	11/05/23	1	PMRX10303
3	11/05/23	2	PMRX10291
4	11/05/23	3	PMRX10263
5	11/05/23	4	PMRX10255
6	11/05/23	5	PMRX10297
Dataset 1

I want to merge them so I will end up with something like this.

White_Eagle_Rail_Car_Data (4).xlsx
	E	F	G	H	I	J	K	L	M	N	O
1	Location	Date	Train #	Ref #	Position	Vehicle ID	Gross	Net	Qty	Load Status	In/Out Status
2	109	11/5/2023	1	1	1	PMRX10303	236000	0	118.00	E	I
3	109	11/5/2023	1	2	2	PMRX10291	236000	0	118.00	E	I
4	109	11/5/2023	1	3	3	PMRX10263	236000	0	118.00	E	I
5	109	11/5/2023	1	4	4	PMRX10255	236000	0	118.00	E	I
6	109	11/5/2023	1	5	5	PMRX10297	236000	0	118.00	E	I
7
8		Calculated
9		Merged
Dataset 1

Cell Formulas
Range	Formula
I2:I6	I2	=H2
K2:K6	K2	=M2*2000

For example, cell A2 in example 2 should end up in cell F2 in example 1. Any assistance is greatly appreciated. thanks!

 

[alansidman]

I don't see a common value/ID that will allow the merger of the two tables. Please clarify. Also don't see the source value for the quantity.

 

[madkinson]

My bad. I cut off the Qty in the second sheet.

What if I used "Position" in the template to match up with "Ref" in the second sheet? They are always unique.

 

[alansidman]

Show us an updated sample and a mocked up solution so that we understand clearly your needs.

 

[madkinson]

File A is the template

Car Data Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Location	Date	Train #	Ref #	Position	Vehicle ID	Gross	Net	Qty	Load Status	In/Out Status
2	109		1		1		0	0		E	I
3	109		1		2		0	0		E	I
4	109		1		3		0	0		E	I
5	109		1		4		0	0		E	I
6	109		1		5		0	0		E	I
7	109		1		6		0	0		E	I
8	109		1		7		0	0		E	I
9	109		1		8		0	0		E	I
10	109		1		9		0	0		E	I
11	109		1		10		0	0		E	I
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=I2*2000

File B is the data from a PLC

Car Data from PLC.xlsx
	A	B	C	D
1	Date	Ref #	Vehicle ID	Weight
2	11/05/23	1	PMRX10303	118.00
3	11/05/23	2	PMRX10291	118.00
4	11/05/23	3	PMRX10263	118.00
5	11/05/23	4	PMRX10255	118.00
6	11/05/23	5	PMRX10297	118.00
7	11/05/23	6	PMRX10212	118.00
8	11/05/23	7	PMRX10289	118.00
9	11/05/23	8	PMRX10293	118.00
10	11/05/23	9	PMRX10201	118.00
11	11/05/23	10	PMRX10275	118.00
Dataset 1

File C is what I would like to end up with.

Car Data Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Location	Date	Train #	Ref #	Position	Vehicle ID	Gross	Net	Qty	Load Status	In/Out Status
2	109	11/5/2023	1	1	1	PMRX10303	236000	0	118.00	E	I
3	109	11/5/2023	1	2	2	PMRX10291	236000	0	118.00	E	I
4	109	11/5/2023	1	3	3	PMRX10263	236000	0	118.00	E	I
5	109	11/5/2023	1	4	4	PMRX10255	236000	0	118.00	E	I
6	109	11/5/2023	1	5	5	PMRX10297	236000	0	118.00	E	I
7	109	11/5/2023	1	6	6	PMRX10212	236000	0	118.00	E	I
8	109	11/5/2023	1	7	7	PMRX10289	236000	0	118.00	E	I
9	109	11/5/2023	1	8	8	PMRX10293	236000	0	118.00	E	I
10	109	11/5/2023	1	9	9	PMRX10201	236000	0	118.00	E	I
11	109	11/5/2023	1	10	10	PMRX10275	236000	0	118.00	E	I
12
13
14		Merged from File
15		Calculated
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=I2*2000

The "Ref#" from the PLC file should match the "Position" in the template on a 1:1 basis.

 

[alansidman]

OK. Thanks for the update. Here is a Power Query Solution. I see that Qty is actually the Weight. Need to change the Mcode to make that happen. See next post for updated Mcode.

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"Position"},T2, {"Ref #"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"Date", "Ref #", "Vehicle ID", "Weight"}, {"Date.1", "Ref #.1", "Vehicle ID.1", "Weight"}),
    RC = Table.RemoveColumns(ET,{"Date", "Ref #", "Vehicle ID", "Weight"}),
    CT = Table.TransformColumnTypes(RC,{{"Date.1", type date}}),
    MoveColumns = Table.ReorderColumns(CT,{"Location", "Date.1", "Train #", "Ref #.1", "Position", "Vehicle ID.1", "Gross", "Net", " Qty ", "Load Status", "In/Out Status"})

in
    MoveColumns

Book14
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Location	Date	Train #	Ref #	Position	Vehicle ID	Gross	Net	Qty	Load Status	In/Out Status		Date	Ref #	Vehicle ID	Weight
2	109		1		1		0	0		E	I		11/5/2023	1	PMRX10303	118
3	109		1		2		0	0		E	I		11/5/2023	2	PMRX10291	118
4	109		1		3		0	0		E	I		11/5/2023	3	PMRX10263	118
5	109		1		4		0	0		E	I		11/5/2023	4	PMRX10255	118
6	109		1		5		0	0		E	I		11/5/2023	5	PMRX10297	118
7	109		1		6		0	0		E	I		11/5/2023	6	PMRX10212	118
8	109		1		7		0	0		E	I		11/5/2023	7	PMRX10289	118
9	109		1		8		0	0		E	I		11/5/2023	8	PMRX10293	118
10	109		1		9		0	0		E	I		11/5/2023	9	PMRX10201	118
11	109		1		10		0	0		E	I		11/5/2023	10	PMRX10275	118
12
13	Location	Date.1	Train #	Ref #.1	Position	Vehicle ID.1	Gross	Net	Qty	Load Status	In/Out Status
14	109	11/5/2023	1	1	1	PMRX10303	0	0		E	I
15	109	11/5/2023	1	2	2	PMRX10291	0	0		E	I
16	109	11/5/2023	1	3	3	PMRX10263	0	0		E	I
17	109	11/5/2023	1	4	4	PMRX10255	0	0		E	I
18	109	11/5/2023	1	5	5	PMRX10297	0	0		E	I
19	109	11/5/2023	1	6	6	PMRX10212	0	0		E	I
20	109	11/5/2023	1	7	7	PMRX10289	0	0		E	I
21	109	11/5/2023	1	8	8	PMRX10293	0	0		E	I
22	109	11/5/2023	1	9	9	PMRX10201	0	0		E	I
23	109	11/5/2023	1	10	10	PMRX10275	0	0		E	I
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=I2*2000

 

[alansidman]

updated Mcode to reflect change of names

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"Position"},T2, {"Ref #"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"Date", "Ref #", "Vehicle ID", "Weight"}, {"Date.1", "Ref #.1", "Vehicle ID.1", "Weight"}),
    RC = Table.RemoveColumns(ET,{"Date", "Ref #", "Vehicle ID", " Qty "}),
    Rename = Table.RenameColumns(RC,{{"Weight", "QTY"}, {"Date.1", "Date"}, {"Ref #.1", "Ref #"}, {"Vehicle ID.1", "Vehicle ID"}}),
    CT = Table.TransformColumnTypes(Rename,{{"Date", type date}}),
    MoveColumns = Table.ReorderColumns(CT,{"Location", "Date", "Train #", "Ref #", "Position", "Vehicle ID", "Gross", "Net", "QTY", "Load Status", "In/Out Status"})

in
    MoveColumns

 

[madkinson]

Thanks! I would have never thought of Power Query.