merge duplicates items without delete duplicates rows

[Hasson]

Hi guys ,
I need merging duplicates items for column F based on column B and the result should be in column G
before

atr.xlsm
	A	B	C	D	E	F
1	DATE	INVO NO	ID	QTY	UNIT PRICE	TOTAL
2	01/05/2023	AS-001	IDTR-100	100.000	100.000	10,000.000
3	02/05/2023	AS-001	IDTR-101	100.000	100.000	10,000.000
4	03/05/2023	AS-002	IDTR-102	20.000	120.000	2,400.000
5	04/05/2023	AS-003	IDTR-103	30.000	140.000	4,200.000
6	05/05/2023	AS-003	IDTR-104	40.000	160.000	6,400.000
7	06/05/2023	AS-003	IDTR-105	50.000	180.000	9,000.000
8	07/05/2023	AS-003	IDTR-106	60.000	200.000	12,000.000
9	08/05/2023	AS-006	IDTR-107	70.000	220.000	15,400.000
10	09/05/2023	AS-007	IDTR-108	80.000	240.000	19,200.000
11	10/05/2023	AS-008	IDTR-109	90.000	260.000	23,400.000
12	11/05/2023	AS-009	IDTR-110	100.000	280.000	28,000.000
13	12/05/2023	AS-009	IDTR-111	110.000	300.000	33,000.000
14	13/05/2023	AS-009	IDTR-112	120.000	320.000	38,400.000
15	14/05/2023	AS-010	IDTR-113	130.000	340.000	44,200.000
INFO

Cell Formulas
Range	Formula
F2:F15	F2	=D2*E2

result

atr.xlsm
	A	B	C	D	E	F	G
1	DATE	INVO NO	ID	QTY	UNIT PRICE	TOTAL
2	01/05/2023	AS-001	IDTR-100	100.000	100.000	10,000.000
3	02/05/2023	AS-001	IDTR-101	100.000	100.000	10,000.000	20,000.000
4	03/05/2023	AS-002	IDTR-102	20.000	120.000	2,400.000	2,400.000
5	04/05/2023	AS-003	IDTR-103	30.000	140.000	4,200.000
6	05/05/2023	AS-003	IDTR-104	40.000	160.000	6,400.000
7	06/05/2023	AS-003	IDTR-105	50.000	180.000	9,000.000
8	07/05/2023	AS-003	IDTR-106	60.000	200.000	12,000.000	31,600.000
9	08/05/2023	AS-006	IDTR-107	70.000	220.000	15,400.000	15,400.000
10	09/05/2023	AS-007	IDTR-108	80.000	240.000	19,200.000	19,200.000
11	10/05/2023	AS-008	IDTR-109	90.000	260.000	23,400.000	23,400.000
12	11/05/2023	AS-009	IDTR-110	100.000	280.000	28,000.000
13	12/05/2023	AS-009	IDTR-111	110.000	300.000	33,000.000
14	13/05/2023	AS-009	IDTR-112	120.000	320.000	38,400.000	99,400.000
15	14/05/2023	AS-010	IDTR-113	130.000	340.000	44,200.000	44,200.000
INFO

Cell Formulas
Range	Formula
G3	G3	=SUM(F2:F3)
G4,G15,G9:G11	G4	=F4
G8	G8	=SUM(F5:F8)
F2:F15	F2	=D2*E2
G14	G14	=SUM(F12:F14)

the data could be 7000 rows.

 

[kweaver]

Try this in G2 and fill down:

=IF(B2=B3,"",SUMIF($B$2:$B$15,B2,$F$2:$F$15))

 

[Hasson]

thanks
but I have big data , do I pull the formula every time when add new data?

 

[kweaver]

Change the reference to row 15 to something large but reasonable. How about 10000? Will that work with future data?

=IF(B2=B3,"",SUMIF($B$2:$B$10000,B2,$F$2:$F$10000))

 

[alansidman]

Would a presentation like the following work for you?

DATE	INVO NO	ID	QTY	UNIT PRICE	TOTAL	Totals
5/1/2023	AS-001	IDTR-100	100	100	10000	20000
5/2/2023	AS-001	IDTR-101	100	100	10000	20000
5/3/2023	AS-002	IDTR-102	20	120	2400	2400
5/4/2023	AS-003	IDTR-103	30	140	4200	31600
5/5/2023	AS-003	IDTR-104	40	160	6400	31600
5/6/2023	AS-003	IDTR-105	50	180	9000	31600
5/7/2023	AS-003	IDTR-106	60	200	12000	31600
5/8/2023	AS-006	IDTR-107	70	220	15400	15400
5/9/2023	AS-007	IDTR-108	80	240	19200	19200
5/10/2023	AS-008	IDTR-109	90	260	23400	23400
5/11/2023	AS-009	IDTR-110	100	280	28000	99400
5/12/2023	AS-009	IDTR-111	110	300	33000	99400
5/13/2023	AS-009	IDTR-112	120	320	38400	99400
5/14/2023	AS-010	IDTR-113	130	340	44200	44200

 

[Hasson]

How about 10000? Will that work with future data?

I'm not sure what you mean . do you mean th formula could not deal with big ?
if it's so , then I think needing vba .

 

[alansidman]

The PQ solution will work with a million rows. If you add data to the original information, PQ will automatically update as the original data will be in a table.

 

[Hasson]

Would a presentation like the following work for you?

thanks but I don't need repeat merging for the same item !