CODEWITHSUNDEEP
excelcellvba
Krzysztof Chrabaszcz
Krzysztof Chrabaszcz

Reputation: 25

Excel unique values for specific numbers in different column

I've got a spreadsheet with 3 columns first one is order number, second one is code for product and third one is the quantity ordered. List got around 40 thousand rows. On the other hand I've got a list of 10 most popular products. I would like to find out how many orders have got more than two of the most popular products on and only them. This is the table:

Table 1

Upvotes: 0

Views: 39

Answers (1)

Evan Callahan
Evan Callahan

Reputation: 79

This isn't the most elegant solution, but should get you the desired results.

=If(Or(A1 = $D$1, A1 = $D$2, A1 = $D$3, ... A1 = $D$10), 1, -1000)

Where the "$D$1..." are the most popular products.

This will give you a value of 1 if an order has one of the 10 most popular products and -1000 if it doesn't. With this you can then use a pivot table and sum this new column and wherever it is 2 or greater is what you are looking for. The -1000 part will make negative any order that has a product that isn't in the 10 most popular which also accounts for the orders with combinations of both.

Upvotes: 1

Related Questions