merge duplicates item for 70K rows and re-price for each sheet individually

[KalilMe]

Hi
I need merging duplicate items for 2500-4000 reference and 70K rows for each sheet individually
so should merge duplicate items for each sheet alone( not across sheets ) based on column C . as to summing values for duplicated item should just be the columns QTY,TOTAL and should ignores columns DATE(should replace of it numbers 1,2,3) , ORDER,PRICE when merging should be as in the result , populate re-price in column G=H/F for result sheets.

COLLECTION .xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	ORDER	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	01/01/2022	OR/1-00	COR-FF1	FR	BANANA	TT	200.00	21.00	4,200.00
3	02/01/2022	OR/1-01	COR-FF2	FR	APPLE	LL	100.00	100.00	10,000.00
4	03/01/2022	OR/1-02	COR-FF3	FR	PEAR	NN	10.00	10.00	100.00
5	04/01/2022	OR/1-03	COR-FF4	FR	BANANA	QQ	20.00	20.00	400.00
6	05/01/2022	OR/1-04	COR-FF5	VEG	TOMATO	SS	12.00	12.00	144.00
7	06/01/2022	OR/1-05	COR-FF6	VEG	TOMATO	AA	12.00	12.00	144.00
8	01/01/2022	OR/1-00	COR-FF1	FR	BANANA	TT	200.00	20.00	4,000.00
STA

Cell Formulas
Range	Formula
I2:I8	I2	=G2*H2

COLLECTION .xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	ORDER	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	01/02/2022	OR/1-11	COR-FF11	FR	PEACH	TT	120.00	22.00	2,640.00
3	02/02/2022	OR/1-12	COR-FF12	FR	APPLE	LL	130.00	23.00	2,990.00
4	03/02/2022	OR/1-13	COR-FF13	FR	PEAR	NN	20.00	12.00	240.00
5	04/02/2022	OR/1-14	COR-FF14	FR	BANANA	QQ	300.00	22.00	6,600.00
6	05/02/2022	OR/1-15	COR-FF15	VEG	TOMATO	SS	234.00	12.00	2,808.00
7	06/02/2022	OR/1-16	COR-FF16	VEG	TOMATO	AA	12.00	12.00	144.00
8	07/02/2022	OR/1-17	COR-FF11	FR	PEACH	TT	200.00	11.00	2,200.00
9	08/02/2022	OR/1-18	COR-FF16	VEG	TOMATO	AA	12.00	10.00	120.00
RPA

Cell Formulas
Range	Formula
I2:I9	I2	=G2*H2

COLLECTION .xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	ORDER	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	21/02/2022	OR/1-21	COR-FF12	FR	APPLE	LL	5.00	23.00	115.00
3	22/02/2022	OR/1-22	COR-FF13	FR	PEAR	NN	2.00	12.00	24.00
4	23/02/2022	OR/1-23	COR-FF11	FR	PEACH	TT	11.00	22.00	242.00
5	24/02/2022	OR/1-24	COR-FF13	FR	PEAR	NN	2.00	12.00	24.00
SR

Cell Formulas
Range	Formula
I2:I5	I2	=G2*H2

result

Result.xlsx
	A	B	C	D	E	F	G	H
1	ITEM	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	UNIT PRICE	TOTAL
2	1	COR-FF1	FR	BANANA	TT	400.00	20.50	8,200.00
3	2	COR-FF2	FR	APPLE	LL	100.00	100.00	10,000.00
4	3	COR-FF3	FR	PEAR	NN	10.00	10.00	100.00
5	4	COR-FF4	FR	BANANA	QQ	20.00	20.00	400.00
6	5	COR-FF5	VEG	TOMATO	SS	12.00	12.00	144.00
7	6	COR-FF6	VEG	TOMATO	AA	12.00	12.00	144.00
STA

Result.xlsx
	A	B	C	D	E	F	G	H
1	ITEM	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	UNIT PRICE	TOTAL
2	1	COR-FF11	FR	PEACH	TT	320.00	15.13	4,840.00
3	2	COR-FF12	FR	APPLE	LL	130.00	23.00	2,990.00
4	3	COR-FF13	FR	PEAR	NN	20.00	12.00	240.00
5	4	COR-FF14	FR	BANANA	QQ	300.00	22.00	6,600.00
6	5	COR-FF15	VEG	TOMATO	SS	234.00	12.00	2,808.00
7	6	COR-FF16	VEG	TOMATO	AA	24.00	12.00	288.00
RPA

Result.xlsx
	A	B	C	D	E	F	G	H
1	ITEM	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	UNIT PRICE	TOTAL
2	1	COR-FF12	FR	APPLE	LL	5.00	23.00	115.00
3	2	COR-FF13	FR	PEAR	NN	4.00	12.00	48.00
4	3	COR-FF11	FR	PEACH	TT	11.00	22.00	242.00
SR

 

[alansidman]

I have only shown the solution for the first example. Once you understand this process, you can apply it to the others

Book2
	A	B	C	D	E	F	G	H	I
1	DATE	ORDER	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	1/1/2022	OR/1-00	COR-FF1	FR	BANANA	TT	200	21	4200
3	2/1/2022	OR/1-01	COR-FF2	FR	APPLE	LL	100	100	10000
4	3/1/2022	OR/1-02	COR-FF3	FR	PEAR	NN	10	10	100
5	4/1/2022	OR/1-03	COR-FF4	FR	BANANA	QQ	20	20	400
6	5/1/2022	OR/1-04	COR-FF5	VEG	TOMATO	SS	12	12	144
7	6/1/2022	OR/1-05	COR-FF6	VEG	TOMATO	AA	12	12	144
8	1/1/2022	OR/1-00	COR-FF1	FR	BANANA	TT	200	20	4000
9
10
11	Index	ORDER	Quantity	RePrice	New Total	Table5 (2).FOOD	Table5 (2).TT-MMN	Table5 (2).ORT-WW
12	1	OR/1-00	400	20.5	8200	FR	BANANA	TT
13	2	OR/1-01	100	100	10000	FR	APPLE	LL
14	3	OR/1-02	10	10	100	FR	PEAR	NN
15	4	OR/1-03	20	20	400	FR	BANANA	QQ
16	5	OR/1-04	12	12	144	VEG	TOMATO	SS
17	6	OR/1-05	12	12	144	VEG	TOMATO	AA
Sheet5

Cell Formulas
Range	Formula
I2:I8	I2	=G2*H2

Load the first table into the Power Query Editor and apply the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ORDER"}, {{"Quantity", each List.Sum([QTY]), type number}, {"RePrice", each List.Average([PRICE]), type number}, {"New Total", each List.Sum([TOTAL]), type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "ORDER", "Quantity", "RePrice", "New Total"})
in
    #"Reordered Columns"

Merge this query back onto the original source data table.

l

et
    Source = Table.NestedJoin(Table5, {"ORDER"}, #"Table5 (2)", {"ORDER"}, "Table5 (2)", JoinKind.LeftOuter),
    #"Expanded Table5 (2)" = Table.ExpandTableColumn(Source, "Table5 (2)", {"FOOD", "TT-MMN", "ORT-WW"}, {"Table5 (2).FOOD", "Table5 (2).TT-MMN", "Table5 (2).ORT-WW"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table5 (2)", {"ORDER"})
in
    #"Removed Duplicates"

 

[KalilMe]

Well, I was waiting for body to gives me macro ,but I will try currently. Where I put this?
Sorry this the first time use PQ.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[KalilMe]

thanks for the links . I will try and inform you how goes .

 

[alansidman]

You are welcome. If you would like to learn more about PQ, then purhcase this book Master Your Data with Power Query in Excel and Power BI

 

[KalilMe]

Hi Alan
I no know what's the problem ! when I enter inside PQ Editor the advanced editor is disabled !

 

[KalilMe]

that's it guys?