Hello,
Below is a condensed table of an orderbook I am currently working on. Each row represents an order. An order consists of an assigned customer, the ordered item, and whether the customer requires special service (indicated by 50/100). The total orderbook contains multiple identical items, but being ordered by multiple customers.
For each item (row), I want to know whether there are more than one distinct customers requiring a '100' service. More specifically: column D should contain a value:
1; if there are more than one customers requiring a '100' service customer
0; if there is only one (or none) customer requiring a '100' service.
So, the following should be created (column D has been manually calculated):
I have tried to calculate column D with Vlookup, and index/match to check for each row whether there are items having >1 customers requiring a '100' service. Also, I tried to sum(countif(...) based on criteria. Unfortunately, I could not manage to create a correct formula. Could you help me out? Thank you.
Below is a condensed table of an orderbook I am currently working on. Each row represents an order. An order consists of an assigned customer, the ordered item, and whether the customer requires special service (indicated by 50/100). The total orderbook contains multiple identical items, but being ordered by multiple customers.
| Customer (column A) | Item (column B) | Required service (column C) | Output (column D) |
| Star | A22EM | 100 | |
| Star | A22EM | 50 | |
| G20 automation | A22IP | 100 | |
| G20 automation | A22IP | 50 | |
| G10 automation corp. | A22IP | 100 | |
| YSAU | A66AA | 50 | |
| YSAU | A77RT | 100 | |
| Fest KG | A77RT | 100 | |
| TR Robotics | A77RT | 50 | |
| Pakvol GmbH | A80GM | 50 | |
| Pakvol GmbH | A80GM | 100 | |
| Pakvol GmbH | A80GM | 50 | |
| TR Robotics | A80GM | 100 | |
| Sau Corp. | A80GM | 50 | |
| Sau Corp. | A80GM | 50 | |
| Sau Corp. | A80GM | 100 | |
| Pakvol GmbH | A80GM | 100 | |
| RC Robotics | A48ZX | 100 | |
| Int. Corp. | A33WB | 50 |
For each item (row), I want to know whether there are more than one distinct customers requiring a '100' service. More specifically: column D should contain a value:
1; if there are more than one customers requiring a '100' service customer
0; if there is only one (or none) customer requiring a '100' service.
So, the following should be created (column D has been manually calculated):
| Customer (column A) | Item (column B) | Required service (column C) | Output (column D) |
| Star | A22EM | 100 | 0 |
| Star | A22EM | 50 | 0 |
| G20 automation | A22IP | 100 | 1 |
| G20 automation | A22IP | 50 | 1 |
| G10 automation corp. | A22IP | 100 | 1 |
| YSAU | A66AA | 50 | 0 |
| YSAU | A77RT | 100 | 1 |
| Fest KG | A77RT | 100 | 1 |
| TR Robotics | A77RT | 50 | 1 |
| Pakvol GmbH | A80GM | 100 | 1 |
| Pakvol GmbH | A80GM | 50 | 1 |
| Pakvol GmbH | A80GM | 50 | 1 |
| TR Robotics | A80GM | 100 | 1 |
| Sau Corp. | A80GM | 50 | 1 |
| Sau Corp. | A80GM | 50 | 1 |
| Sau Corp. | A80GM | 100 | 1 |
| Pakvol GmbH | A80GM | 50 | 1 |
| RC Robotics | A48ZX | 100 | 0 |
| Int. Corp. | A33WB | 50 | 0 |
I have tried to calculate column D with Vlookup, and index/match to check for each row whether there are items having >1 customers requiring a '100' service. Also, I tried to sum(countif(...) based on criteria. Unfortunately, I could not manage to create a correct formula. Could you help me out? Thank you.
