Merge duplicates Batches based on column contains the same price

[MKLAQ]

Hello,
I have duplicates BATCH in column B ,contains many prices in column G . if the duplicates BATCH contains the same price then merge for column F , otherwise don't merge.
when merge duplicates batch should be based on column B .
the batches could be 2000 and repeated batches could reach 6000 rows.
I would merge in the same sheet

mkk ‫‬.xlsx
	A	B	C	D	E	F	G	H
8	ITEM	BATCH	GOODS	TYPE	MODEL	IMPORT	PRICE	TOTAL
9	1	CCR-001	CR CCR-1	2010	BMW	380.00	12,000.00	4,560,000.00
10	2	CCR-001	CR CCR-1	2010	BMW	120.00	12,000.00	1,440,000.00
11	3	CCR-002	TR CCB-3	TCR 1-TT	2009 MER	120.00	11,000.00	1,320,000.00
12	4	CCR-003	CCR-2	FI 2012	TIGUAN	120.00	12,500.00	1,500,000.00
13	5	CCR-003	CCR-2	FI 2012	TIGUAN	100.00	12,300.00	1,230,000.00
14	6	CCR-004	TR CCB-3	TMIR 1-TT	2009 MER	101.00	4,400.00	444,400.00
15	7	CCR-004	TR CCB-3	TMIR 1-TT	2009 MER	80.00	4,400.00	352,000.00
16	TOTAL							10,846,400.00
CA

Cell Formulas
Range	Formula
H9:H15	H9	=F9*G9
H16	H16	=SUM(H9:H15)

should be like this

mkk ‫‬.xlsx
	A	B	C	D	E	F	G	H
8	ITEM	BATCH	GOODS	TYPE	MODEL	IMPORT	PRICE	TOTAL
9	1	CCR-001	CR CCR-1	2010	BMW	500.00	12,000.00	6,000,000.00
10	2	CCR-002	TR CCB-3	TCR 1-TT	2009 MER	120.00	11,000.00	1,320,000.00
11	3	CCR-003	CCR-2	FI 2012	TIGUAN	120.00	12,500.00	1,500,000.00
12	4	CCR-003	CCR-2	FI 2012	TIGUAN	100.00	12,300.00	1,230,000.00
13	5	CCR-004	TR CCB-3	TMIR 1-TT	2009 MER	181.00	4,400.00	796,400.00
14	TOTAL							10,846,400.00
CA

Cell Formulas
Range	Formula
H9:H13	H9	=F9*G9
H14	H14	=SUM(H9:H13)

 

[dermie_72]

you could use a pivot table with the following setting to provide this. See if that helps.

 

[alansidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"ITEM"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"BATCH", "GOODS", "TYPE", "MODEL", "PRICE"}, {{"Import", each List.Sum([IMPORT]), type number}, {"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", "BATCH", "GOODS", "TYPE", "MODEL", "PRICE", "Import", "Total"})
in
    #"Reordered Columns"

 

[MKLAQ]

@dermie_72
can you show me result by PT , please?

 

[dermie_72]

@dermie_72
can you show me result by PT , please?

 

[MKLAQ]

I don't prefer this way, sorry .
If it's by vba will be ok for me.

 

[MKLAQ]

@alansidman
by the way gives error

= Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type)

 

[alansidman]

What is the error message. Can't help diagnose the unknown.

 

[MKLAQ]

here is error
: 5 arguments were passed to a function that expects between 2 and 4.
Details:
Pattern=
Arguments=List

 

[alansidman]

Did you amend the Mcode in any manner? There are only four arguments in that line of code. Make sure that there is a comma after the last closed parens.

Here is my copy of your worksheet.

PQ - MKLAQ.xlsx | Powered by Box

app.box.com