Removing or highlighting duplicate values from same row (client order) (but not from a different order)

Question

I have let's say, some client orders (each order is different or belongs to different client) and I want in the same order to check if there is a duplicate model input on the model category as shown below. For example in order 1 there are two models with same name (sm150, 1st and last) and i want to find a way to highlight it or to show a warning message or even better to identify it and automatically remove it OR add the remaining stock and price to the first row's stock and price for the same model.

Thank you in advance for your help.

I tried with filter function to separate each order and then countif but becomes really hard computation wise, for excel to check each different product.

Sample data:

Ord -1			Mic Shura	sm150	test22	9	9	25/3/2024	26/3/2024
			Piano Thomman	cronos	test23	7	8	25/3/2024	26/3/2024
			Mic Shura	sm150b	test24	6	6	25/3/2024	26/3/2024
			Mic Shura	sm150	test23	8	7	25/3/2024	26/3/2024
Ord-2			Mic Shura	sm150c	none	12	10	25/3/2024	26/3/2024
			Guitar Bayer	bayleaf	none	9	98	25/3/2024	26/3/2024
			Guitar Siem	yama5000	no	1	8	25/3/2024	26/3/2024

Answer 1

I will just assume you have put your data like below in A1:D5:

model	accessories	stock	cost
sm150	test22	9	9
cronos	test23	7	8
sm150b	test24	6	6
sm150	test23	8	7

To group data by Model, and sum up Stock and Cost, you can use below formula:

=LET(models, A2:A5, unique_models, UNIQUE(models,FALSE,FALSE),stocks,C2:C5, costs, D2:D5, HSTACK(unique_models, MAP(unique_models,LAMBDA(x,SUMPRODUCT(stocks,--(models=x)))), MAP(unique_models,LAMBDA(x,SUMPRODUCT(costs,--(models=x))))))

Basically, the formula is made up of two maps:

The first one sums up Stocks by model:

MAP(unique_models,LAMBDA(x,SUMPRODUCT(stocks,--(models=x))))

The second one sums up Costs by model:

MAP(unique_models,LAMBDA(x,SUMPRODUCT(stocks,--(models=x))))

The results is like below:

Alternatively, if you have GROUPBY, you can just use this:

=GROUPBY(A1:A5,C1:D5,SUM,3,0)

Apologies I didn't realise you cannot use MAP. You mentioned your table is in E2:H2000, so you can add another input below formula in column I:

=COUNTIF($E$2:$E$2000;E2)>1

As for conditional formatting, here is how the rule should be set up:

Then in column J, you can add below formula to sum stock.

=IF(I2;SUMIF($E$2:$E$2000;E2;$G$2:$G$2000);G2)

In column K, use below to sum costs:

=IF($I2;SUMIF($E$2:$E$2000;$E2;$H$2:$H$2000);$H2)