merge duplicates items for more than 16000 rows & 150 columns across sheets

[Alaa mg]

Hi

I search for macro to merge duplicates items for about 16000 rows & 150 columns for each sheet

so should match items in column B across sheets and merge duplicates items and summing the values for each month whether there are duplicates items or new items , should show the whole data in sheet summary and finally should insert column AGGREGATE to sum the whole values for all of the months .

every time I will add new sheets and sometimes changes data in sheets have already existed then should update data in sheet summary

as to columns every time I add new columns based on current month for each sheet .
some data in some sheets

MUL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ITEM	ID	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR
2	1	MT ASGL-VEN SD-1	12	10	-	-	-	-	-	-	-	-	-	-	-	-	44	-
3	2	C-ZER	130	-	-	11	-	-	-	-	-	-	-	-	-	-	-	-
4	3	BB 2000MN TT GH	90	-	-	-	-	-	1	11	-	-	-	-	-	-	-	-
5	4	TRM 2000MN TT GH	-	16	-	11	1	-	-	123	-	33	-	-	33	-	-	-
6	5	BD 234 NH GG	78	70	-	-	-	-	-	-	-	-	-	-	-	-	-	-
7	6	BR 111/33M ER HJ	-	-	-	-	-	-	-	-	-	-	-	-	66	80		1
8	7	VB 1222** SS TF HJK	45	-	-	-	-	-	-	-	-	-	-	-	23	34	-	-
9	8	VB 145** SS TF HJK	-	45	-	-	-	-	-	111	23	-	-	-	-	-	-	-
10	9	DEWR 1222** SS TF HJK	34	34	-	-	-	-	-	-	-	-	-	-	-	-	-	-
11	10	DAS 1222** SS TF HJK	-	35	-	-	-	-	-	-	-	-	-	-	-	-	-	44
DA

MUL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ITEM	ID	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV
2	2	C-ZER	22	-	-	11	-	-	-	-	-	-	-
3	10	DAS 1222** SS TF HJK	-	45	-	-	-	-	-	-	-	-	-
4	1	MT ASGL-VEN SD-1	34	10	-	-	-	-	22	-	-	-	12
5	3	MT ASGL-VEN SD-2	22	-	-	-	-	-	-	-	-	-	30
6	8	VB 145** SS TF HJK	-	56	-	-	-	-	12	-	-	-	-
7	9	DEWR 1222** SS TF HJK	123	40	-	-	-	-	-	-	-	-	12
MA

MUL.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ID	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY
2	BD 234 NH GG	70	70	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
3	BR 111/33M ER HJ	66	80	-	-	-	-	-	-	-	-	-	-	-	-	33	-	-
4	VB 1222** SS TF HJK	45	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
5	C-ZER	130	-	-	11	-	-	-	-	-	-	-	-	-	-	-	-	-
6	BB 2000MN TT GH	90	-	-	-	-	-	1	11	-	-	-	-	-	-	-	-	1
7	TRM 2000MN TT GH	-	16	-	11	1	-	-	123	-	33	-	-	33	-	-	6	-
8	MT ASGL-VEN SD-1	12	10	-	-	-	-	-	-	-	-	-	-	-	-	44	-	-
9	VB 145** SS TF HJK	-	45	-	-	-	-	-	111	23	-	-	-	-	-	-	-	-
10	BVG2340/123-BBG12	-	12	-	-	-	230	-			-	-	-	110	-	-	-	-
SE

my goal should be like this

MUL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ITEM	ID	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	AGGREGATE
2	1	MT ASGL-VEN SD-1	58	30	-	-	-	-	22	-	-	-	-	-	-	-	88		-	198
3	2	C-ZER	282	-	-	33	-	-	-	-	-	-	-	-	-	-	-	-	-	315
4	3	BB 2000MN TT GH	180	-	-	-	-	-	2	22	-	-	-	-	-	-	-	-	1	205
5	4	TRM 2000MN TT GH	-	32	-	22	2	-	-	246	-	66	-	-	66	-	-	6	-	440
6	5	BD 234 NH GG	148	140	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	288
7	6	BR 111/33M ER HJ	66	80	-	-	-	-	-	-	-	-	-	-	66	80	33	-	-	325
8	7	VB 1222** SS TF HJK	90	-	-	-	-	-	-	-	-	-	-	-	23	34	-	-	-	147
9	8	VB 145** SS TF HJK	-	146	-	-	-	-	12	222	46	-	-	-	-	-	-	-	-	426
10	9	DEWR 1222** SS TF HJK	157	74	-	-	-	-	-	-	-	-	12	-	-	-	-	-	-	243
11	10	DAS 1222** SS TF HJK	-	80	-	-	-	-	-	-	-	-	-	-	-	-	-	44	-	124
12	11	MT ASGL-VEN SD-2	22	-	-	-	-	-	-	-	-	-	30	-	-	-	-	-	-	52
13	12	BVG2340/123-BBG12	-	12	-	-	-	230	-			-	-	-	110	-	-	-	-	352
SUMMARY

 

[jdellasala]

After pulling in the three source tables into Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"-","",Replacer.ReplaceValue,{"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC", "JAN2", "FEB3", "MAR4", "APR5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}, {"JAN2", Int64.Type}, {"FEB3", Int64.Type}, {"MAR4", Int64.Type}, {"APR5", Int64.Type}, {"ID", type text}, {"ITEM", Int64.Type}})
in
    #"Changed Type"

(Repeated with Table2 and Table3), they were appended to each other, and then each ID was grouped with each month summed:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    RemovedItem = Table.RemoveColumns(Source,{"ITEM"}),
    #"Grouped Rows" = Table.Group(RemovedItem, {"ID"}, {{"Jan", each List.Sum([JAN]), type nullable number}, {"Feb", each List.Sum([FEB]), type nullable number}, {"Mar", each List.Sum([MAR]), type nullable number}, {"Apr", each List.Sum([APR]), type nullable number}, {"May", each List.Sum([MAY]), type nullable number}, {"Jun", each List.Sum([JUN]), type nullable number}, {"Jul", each List.Sum([JUL]), type nullable number}, {"Aug", each List.Sum([AUG]), type nullable number}, {"Sep", each List.Sum([SEP]), type nullable number}, {"Oct", each List.Sum([OCT]), type nullable number}, {"Nov", each List.Sum([NOV]), type nullable number}, {"Dec", each List.Sum([DEC]), type nullable number}, {"Jan2", each List.Sum([JAN2]), type nullable number}, {"Feb3", each List.Sum([FEB3]), type nullable number}, {"Mar4", each List.Sum([MAR4]), type nullable number}, {"Apr5", each List.Sum([APR5]), type nullable number}, {"May6", each List.Sum([MAY6]), type nullable number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Item", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Item", "ID", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan2", "Feb3", "Mar4", "Apr5", "May6"})
in
    #"Reordered Columns"

which yielded this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
35	Item	ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan2	Feb3	Mar4	Apr5	May6
36	1	MT ASGL-VEN SD-1	58	30					22				12				88
37	2	C-ZER	282			33
38	3	BB 2000MN TT GH	180						2	22									1
39	4	TRM 2000MN TT GH		32		22	2			246		66			66			6
40	5	BD 234 NH GG	148	140
41	6	BR 111/33M ER HJ	66	80											66	80	33	1
42	7	VB 1222** SS TF HJK	90												23	34
43	8	VB 145** SS TF HJK		146					12	222	46
44	9	DEWR 1222** SS TF HJK	157	74									12
45	10	DAS 1222** SS TF HJK		80														44
46	11	MT ASGL-VEN SD-2	22										30
47	12	BVG2340/123-BBG12		12				230							110
Sheet2

You cannot have duplicate column headers in PQ which is why the second instances of months have numbers. There is no doubt that someone better versed in M Language could do the summing part of the Grouping more efficiently, and one down side is that if additional months were added to the largest table (Jun, Jul), they would not aggregate properly without this code being adjusted.
However, this was nearly all done with the Power Query UI (I manually renamed Index to Item), and as the three source tables updated, the final table would just need a refresh.

 

[jdellasala]

The grouping and adding a SUM aggregation for each column was a pain! It occurred to me that a straight APPEND of the 3 source tables, loaded as Connections Only to the Data Model would allow for a much faster and easier way to get the desired information by loading the data to a Pivot Table:

Book1
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
6	Row Labels	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN2	FEB3	MAR4	APR5	MAY6
7	BB 2000MN TT GH	180						2	22									1
8	BD 234 NH GG	148	140
9	BR 111/33M ER HJ	66	80											66	80	33	1
10	BVG2340/123-BBG12		12				230							110
11	C-ZER	282			33
12	DAS 1222** SS TF HJK		80														44
13	DEWR 1222** SS TF HJK	157	74									12
14	MT ASGL-VEN SD-1	58	30					22				12				88
15	MT ASGL-VEN SD-2	22										30
16	TRM 2000MN TT GH		32		22	2			246		66			66			6
17	VB 1222** SS TF HJK	90												23	34
18	VB 145** SS TF HJK		146					12	222	46
19	Grand Total	1003	594		55	2	230	36	490	46	66	54		265	114	121	51	1
Sheet2

The first final output wasn't sorted, so here it is with the ID Sorted for comparison:

Book1
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
10	Item	ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan2	Feb3	Mar4	Apr5	May6
11	3	BB 2000MN TT GH	180						2	22									1
12	5	BD 234 NH GG	148	140
13	6	BR 111/33M ER HJ	66	80											66	80	33	1
14	12	BVG2340/123-BBG12		12				230							110
15	2	C-ZER	282			33
16	10	DAS 1222** SS TF HJK		80														44
17	9	DEWR 1222** SS TF HJK	157	74									12
18	1	MT ASGL-VEN SD-1	58	30					22				12				88
19	11	MT ASGL-VEN SD-2	22										30
20	4	TRM 2000MN TT GH		32		22	2			246		66			66			6
21	7	VB 1222** SS TF HJK	90												23	34
22	8	VB 145** SS TF HJK		146					12	222	46
Sheet2

 

[Alaa mg]

thanks
but I said

I search for macro

You cannot have duplicate column headers in PQ which is why the second instances of months have numbers.

I believe the macro does that even if there is duplicates MONTH name , could depends start from the beginning column to end column not the headers , so in this case duplicates MONTH name doesn't not affect in my view .

 

[Alaa mg]

Could be vba experts?

 

[Alaa mg]