0

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.

excel img example

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
Improve this question
2
  • 1
    Can you put your sample data in a table using this markdown? Commented Mar 27, 2024 at 9:59
  • 1
    Please post sample data using Markdown_Generator and show your expected output Commented Mar 27, 2024 at 12:25

1 Answer 1

Reset to default
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:

enter image description here

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

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

enter image description here

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:

enter image description here

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

thank you so much for your help. I am getting a #Name error though. Since your solution is too complicated for me, if you had time I would love to learn what its function does and process of your thinking! I modified only the tables as follows but its producing an error: =LET(models; E2:E2000; unique_models; UNIQUE(models;FALSE;FALSE);stocks;G2:G2000; costs; H2:H2000; HSTACK(unique_models; MAP(unique_models;LAMBDA(x;SUMPRODUCT(stocks;--(models=x)))); MAP(unique_models;LAMBDA(x;SUMPRODUCT(costs;--(models=x)))))) The error seems to be on the models variable you create
Also since I can not change the table, would it be possible for excel to just identify the duplicate and highlight it or just add up the cost and stock in one row. Maybe with VBA but that becomes more complicated for no reason. Lastly, i think I can not use the map function since it does not exist on my excel pro plus2021
Sorry, I saw you mentioned FILTER so I just assume MAP is also available in your excel
If you want to check duplicates, you can use =COUNTIF($E$2:$E$2000;E2)>1, and then use conditional formating.
yeap did it tnx u for the time and effort. I decided not to use sumif coz i grouped the price per order in another column.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.