collection duplicate items for each sheet alone based on column

[Mussa]

hi experts
I search so much to merge duplicate item for each sheet separatly but I don't find thread like what I want .so I have about five sheets are the same structure should collection based on COL B and COL C,D should merge with comma when there are many differnt numbers for duplicate item and summing the value in COL H but in COL I(UNIT PRICE) should not summing . should calulate the price average because some times the same item changes in the price and COL J= COL H x COL I
with considering I have data about 6000 rows across multiple sheets and increasable

first

KarmaTest.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE1	PRICE2
2	1/1/2021	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	1,000.00	145.00	166.00
3	1/2/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	500.00	200.00	230.00
4	1/3/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	3,456.00	130.00	140.00
5	1/4/2021	A12	BlS1/12-024	CC-MMSN1/15	AC-AD4	AA-103	TTY4	654.00	150.00	199.00
6	1/5/2021	A06	BlS1/12-025	CC-MMSN1/16	AC-AD5	AA-104	TTY5	321.00	160.00	170.00
7	1/6/2021	A07	BlS1/12-026	CC-MMSN1/17	AC-AD6	AA-105	TTY6	234.00	180.00	200.00
8	1/7/2021	A08	BlS1/12-027	CC-MMSN1/18	AC-AD7	AA-106	TTY7	456.00	123.00	130.00
9	1/8/2021	A09	BlS1/12-028	CC-MMSN1/19	AC-AD8	AA-107	TTY8	789.00	134.00	155.00
10	1/9/2021	A10	BlS1/12-029	CC-MMSN1/20	AC-AD9	AA-108	TTY9	890.00	123.00	156.00
11	1/10/2021	A13	BlS1/12-030	CC-MMSN1/21	AC-AD10	AA-109	TTY10	780.00	134.00	144.00
12	1/11/2021	A14	BlS1/12-031	CC-MMSN1/22	AC-AD11	AA-110	TTY11	670.00	145.00	150.00
13	1/12/2021	A01	BlS1/12-032	CC-MMSN1/23	AC-AD12	AA-111	TTY12	345.00	156.00	170.00
14	1/13/2021	A01	BlS1/12-033	CC-MMSN1/24	AC-AD13	AA-112	TTY13	678.00	177.00	180.00
15	1/14/2021	A01	BlS1/12-034	CC-MMSN1/25	AC-AD14	AA-113	TTY14	1,234.00	166.00	200.00
16	1/15/2021	A02	BlS1/12-035	CC-MMSN1/26	AC-AD1	AA-100	TTY1	200.00	145.00	166.00
17	1/16/2021	A03	BlS1/12-036	CC-MMSN1/27	AC-AD2	AA-101	TTY2	100.00	200.00	230.00
18	1/17/2021	A04	BlS1/12-037	CC-MMSN1/28	AC-AD3	AA-102	TTY3	100.00	130.00	140.00
19	1/18/2021	A12	BlS1/12-038	CC-MMSN1/29	AC-AD4	AA-103	TTY4	200.00	150.00	199.00
20	1/19/2021	A06	BlS1/12-039	CC-MMSN1/30	AC-AD5	AA-104	TTY5	200.00	160.00	170.00
21	1/20/2021	A07	BlS1/12-040	CC-MMSN1/31	AC-AD6	AA-105	TTY6	240.00	180.00	200.00
22	1/21/2021	A08	BlS1/12-041	CC-MMSN1/32	AC-AD7	AA-106	TTY7	250.00	123.00	130.00
23	1/22/2021	A09	BlS1/12-042	CC-MMSN1/33	AC-AD8	AA-107	TTY8	260.00	134.00	155.00
24	1/23/2021	A10	BlS1/12-043	CC-MMSN1/34	AC-AD9	AA-108	TTY9	270.00	123.00	156.00
25	1/24/2021	A13	BlS1/12-044	CC-MMSN1/35	AC-AD10	AA-109	TTY10	280.00	134.00	144.00
26	1/25/2021	A14	BlS1/12-045	CC-MMSN1/36	AC-AD11	AA-110	TTY11	200.00	145.00	150.00
27	1/26/2021	A01	BlS1/12-046	CC-MMSN1/37	AC-AD12	AA-111	TTY12	190.00	156.00	170.00
28	1/27/2021	A01	BlS1/12-047	CC-MMSN1/38	AC-AD13	AA-112	TTY13	100.00	177.00	180.00
29	1/28/2021	A01	BlS1/12-048	CC-MMSN1/39	AC-AD14	AA-113	TTY14	100.00	166.00	200.00
30
lkj

second

KarmaTest.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	2/2/2021	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	20	146.00	2,920.00
3	2/3/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	39	200.00	7,800.00
4	2/4/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	56	130.00	7,280.00
5	2/5/2021	A12	BlS1/12-024	CC-MMSN1/15	AC-AD4	AA-103	TTY4	77	150.00	11,550.00
6	2/6/2021	A06	BlS1/12-025	CC-MMSN1/16	AC-AD5	AA-104	TTY5	66	160.00	10,560.00
7	2/7/2021	A07	BlS1/12-026	CC-MMSN1/17	AC-AD6	AA-105	TTY6	88	180.00	15,840.00
8	2/8/2021	A08	BlS1/12-027	CC-MMSN1/18	AC-AD7	AA-106	TTY7	345	123.00	42,435.00
9	2/9/2021	A09	BlS1/12-028	CC-MMSN1/19	AC-AD8	AA-107	TTY8	123	134.00	16,482.00
10	2/10/2021	A10	BlS1/12-029	CC-MMSN1/20	AC-AD9	AA-108	TTY9	44	123.00	5,412.00
11	2/11/2021	A13	BlS1/12-030	CC-MMSN1/21	AC-AD10	AA-109	TTY10	56	134.00	7,504.00
12	2/12/2021	A14	BlS1/12-031	CC-MMSN1/22	AC-AD11	AA-110	TTY11	345	145.00	50,025.00
13	2/13/2021	A15	BlS1/12-032	CC-MMSN1/23	AC-AD12	AA-111	TTY12	234	156.00	36,504.00
14	2/14/2021	A16	BlS1/12-033	CC-MMSN1/24	AC-AD13	AA-112	TTY13	123	177.00	21,771.00
15	2/15/2021	A17	BlS1/12-034	CC-MMSN1/25	AC-AD14	AA-113	TTY14	6	166.00	996.00
16	2/16/2021	A02	BlS1/12-035	CC-MMSN1/26	AC-AD1	AA-100	TTY1	20	147.00	2,940.00
17	2/17/2021	A03	BlS1/12-036	CC-MMSN1/27	AC-AD2	AA-101	TTY2	39	200.00	7,800.00
18	2/18/2021	A04	BlS1/12-037	CC-MMSN1/28	AC-AD3	AA-102	TTY3	56	130.00	7,280.00
19	2/19/2021	A12	BlS1/12-038	CC-MMSN1/29	AC-AD4	AA-103	TTY4	77	150.00	11,550.00
20	2/20/2021	A06	BlS1/12-039	CC-MMSN1/30	AC-AD5	AA-104	TTY5	66	160.00	10,560.00
21	2/21/2021	A07	BlS1/12-040	CC-MMSN1/31	AC-AD6	AA-105	TTY6	88	180.00	15,840.00
22	2/22/2021	A08	BlS1/12-041	CC-MMSN1/32	AC-AD7	AA-106	TTY7	345	123.00	42,435.00
23	2/23/2021	A09	BlS1/12-042	CC-MMSN1/33	AC-AD8	AA-107	TTY8	123	134.00	16,482.00
24	2/24/2021	A10	BlS1/12-043	CC-MMSN1/34	AC-AD9	AA-108	TTY9	44	123.00	5,412.00
25	2/25/2021	A13	BlS1/12-044	CC-MMSN1/35	AC-AD10	AA-109	TTY10	56	134.00	7,504.00
26	2/26/2021	A14	BlS1/12-045	CC-MMSN1/36	AC-AD11	AA-110	TTY11	345	145.00	50,025.00
27	2/27/2021	A15	BlS1/12-046	CC-MMSN1/37	AC-AD12	AA-111	TTY12	234	156.00	36,504.00
28	2/28/2021	A16	BlS1/12-047	CC-MMSN1/38	AC-AD13	AA-112	TTY13	123	177.00	21,771.00
29	3/1/2021	A17	BlS1/12-048	CC-MMSN1/39	AC-AD14	AA-113	TTY14	6	166.00	996.00
SSRR

Cell Formulas
Range	Formula
J2:J29	J2	=I2*H2

third

KarmaTest.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	3/1/2021	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	123	146.00	17,958.00
3	3/2/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	44	200.00	8,800.00
4	3/3/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	33	130.00	4,290.00
5	3/4/2021	A12	BlS1/12-024	CC-MMSN1/15	AC-AD4	AA-103	TTY4	22	150.00	3,300.00
6	3/5/2021	A06	BlS1/12-025	CC-MMSN1/16	AC-AD5	AA-104	TTY5	124	160.00	19,840.00
7	3/6/2021	A07	BlS1/12-026	CC-MMSN1/17	AC-AD6	AA-105	TTY6	56	180.00	10,080.00
8	3/7/2021	A08	BlS1/12-027	CC-MMSN1/18	AC-AD7	AA-106	TTY7	77	123.00	9,471.00
9	3/8/2021	A09	BlS1/12-028	CC-MMSN1/19	AC-AD8	AA-107	TTY8	88	134.00	11,792.00
10	3/9/2021	A10	BlS1/12-029	CC-MMSN1/20	AC-AD9	AA-108	TTY9	99	123.00	12,177.00
11	3/10/2021	A13	BlS1/12-030	CC-MMSN1/21	AC-AD10	AA-109	TTY10	87	134.00	11,658.00
12	3/11/2021	A14	BlS1/12-031	CC-MMSN1/22	AC-AD11	AA-110	TTY11	65	145.00	9,425.00
13	3/12/2021	A15	BlS1/12-032	CC-MMSN1/23	AC-AD12	AA-111	TTY12	43	156.00	6,708.00
14	3/13/2021	A16	BlS1/12-033	CC-MMSN1/24	AC-AD13	AA-112	TTY13	134	177.00	23,718.00
15	3/14/2021	A17	BlS1/12-034	CC-MMSN1/25	AC-AD14	AA-113	TTY14	23	166.00	3,818.00
16	3/15/2021	A02	BlS1/12-035	CC-MMSN1/26	AC-AD1	AA-100	TTY1	123	146.00	17,958.00
17	3/16/2021	A03	BlS1/12-036	CC-MMSN1/27	AC-AD2	AA-101	TTY2	44	200.00	8,800.00
18	3/17/2021	A04	BlS1/12-037	CC-MMSN1/28	AC-AD3	AA-102	TTY3	33	130.00	4,290.00
19	3/18/2021	A12	BlS1/12-038	CC-MMSN1/29	AC-AD4	AA-103	TTY4	22	150.00	3,300.00
20	3/19/2021	A06	BlS1/12-039	CC-MMSN1/30	AC-AD5	AA-104	TTY5	124	160.00	19,840.00
21	3/20/2021	A07	BlS1/12-040	CC-MMSN1/31	AC-AD6	AA-105	TTY6	56	180.00	10,080.00
22	3/21/2021	A08	BlS1/12-041	CC-MMSN1/32	AC-AD7	AA-106	TTY7	77	123.00	9,471.00
23	3/22/2021	A09	BlS1/12-042	CC-MMSN1/33	AC-AD8	AA-107	TTY8	88	134.00	11,792.00
24	3/23/2021	A10	BlS1/12-043	CC-MMSN1/34	AC-AD9	AA-108	TTY9	99	123.00	12,177.00
25	3/24/2021	A13	BlS1/12-044	CC-MMSN1/35	AC-AD10	AA-109	TTY10	87	134.00	11,658.00
26	3/25/2021	A14	BlS1/12-045	CC-MMSN1/36	AC-AD11	AA-110	TTY11	65	145.00	9,425.00
27	3/26/2021	A15	BlS1/12-046	CC-MMSN1/37	AC-AD12	AA-111	TTY12	43	156.00	6,708.00
28	3/27/2021	A16	BlS1/12-047	CC-MMSN1/38	AC-AD13	AA-112	TTY13	134	177.00	23,718.00
29	3/28/2021	A17	BlS1/12-048	CC-MMSN1/39	AC-AD14	AA-113	TTY14	23	166.00	3,818.00
TTTR

Cell Formulas
Range	Formula
J2:J29	J2	=I2*H2

fourth

KarmaTest.xlsm
	B	C	D	E	F	G	H	I	J
1	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	140.00	1,400.00
3	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	200.00	2,400.00
4	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130.00	2,080.00
5	A02	BlS1/12-024	CC-MMSN1/15	AC-AD1	AA-100	TTY1	10	146.00	1,460.00
6	A03	BlS1/12-025	CC-MMSN1/16	AC-AD2	AA-101	TTY2	12	200.00	2,400.00
7	A04	BlS1/12-026	CC-MMSN1/17	AC-AD3	AA-102	TTY3	16	144.00	2,304.00
LLLR

Cell Formulas
Range	Formula
J2:J7	J2	=I2*H2

fifth

KarmaTest.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	5/1/2021	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	146.00	1,460.00
3	5/2/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	200.00	2,400.00
4	5/3/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130.00	2,080.00
5	5/4/2021	A12	BlS1/12-024	CC-MMSN1/15	AC-AD4	AA-103	TTY4	15	146.00	2,190.00
6	5/5/2021	A06	BlS1/12-025	CC-MMSN1/16	AC-AD5	AA-104	TTY5	18	200.00	3,600.00
7	5/6/2021	A07	BlS1/12-026	CC-MMSN1/17	AC-AD6	AA-105	TTY6	4	130.00	520.00
8	5/7/2021	A08	BlS1/12-027	CC-MMSN1/18	AC-AD7	AA-106	TTY7	3	130.00	390.00
9	5/8/2021	A09	BlS1/12-028	CC-MMSN1/19	AC-AD8	AA-107	TTY8	4	130.00	520.00
10	5/9/2021	A02	BlS1/12-029	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	130.00	1,300.00
11	5/10/2021	A03	BlS1/12-030	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	130.00	1,560.00
12	5/11/2021	A04	BlS1/12-031	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130.00	2,080.00
13	5/12/2021	A12	BlS1/12-032	CC-MMSN1/15	AC-AD4	AA-103	TTY4	15	130.00	1,950.00
14	5/13/2021	A06	BlS1/12-033	CC-MMSN1/16	AC-AD5	AA-104	TTY5	18	130.00	2,340.00
15	5/14/2021	A07	BlS1/12-034	CC-MMSN1/17	AC-AD6	AA-105	TTY6	4	130.00	520.00
16	5/15/2021	A08	BlS1/12-035	CC-MMSN1/18	AC-AD7	AA-106	TTY7	3	130.00	390.00
17	5/16/2021	A09	BlS1/12-036	CC-MMSN1/19	AC-AD8	AA-107	TTY8	4	130.00	520.00
18	5/17/2021	A07	BlS1/12-037	CC-MMSN1/20	AC-AD6	AA-105	TTY6	4	140.00	560.00
19	5/18/2021	A08	BlS1/12-038	CC-MMSN1/21	AC-AD7	AA-106	TTY7	3	145.00	435.00
20	5/19/2021	A09	BlS1/12-039	CC-MMSN1/22	AC-AD8	AA-107	TTY8	4	155.00	620.00
JJY

Cell Formulas
Range	Formula
J2:J20	J2	=I2*H2

I will take fourth sheet for example as expected result

KarmaTest.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	4/1/2021	A02	BlS1/12-021,24	CC-MMSN1/12,15	AC-AD1	AA-100	TTY1	20	134.00	2,680.00
3	4/2/2021	A03	BlS1/12-022,25	CC-MMSN1/13	AC-AD2	AA-101	TTY2	24	205.00	4,920.00
4	4/3/2021	A04	BlS1/12-023,26	CC-MMSN1/14,17	AC-AD3	AA-102	TTY3	32	137.00	4,384.00
LLLR

Cell Formulas
Range	Formula
J2:J4	J2	=I2*H2

in short words should merge the all of data based on duplicate items in COL B for each sheet alone with calculate price average .
thanks in advance

 

[Mussa]

 

[Mussa]

any assistance?