eggplant_casserole
New Member
- Joined
- Jul 5, 2011
- Messages
- 4
I have a worksheet (Microsoft 365 Family) with a long list of items (over 2,000). It is an inventory. The items are stored in various locations, with a column for each location. Some items will be duplicated in multiple locations.
The entry in the column differs depending on condition. It may simply be a YES but it could say INCOMPLETE or something like that. Consequently, I used COUNTA to do a count of how many items are in a location.
What I want to do is have a record of how many unique items there are in a location (that is, a cell in the column is counted if it isn't blank and if its neighbouring cell in the other qualifying columns is blank). How do I go about this? As an example:
Here, the relevant columns are D to H, and rows 2 to 11. The last row should read 1, 1, 2, 2, 1 as there are duplicates in rows 2, 5 and 10.
It might also be useful to have a tally of how many items are duplicated in more than one location, which would obviously be one result rather than a result per column.
Thanks in advance!
The entry in the column differs depending on condition. It may simply be a YES but it could say INCOMPLETE or something like that. Consequently, I used COUNTA to do a count of how many items are in a location.
What I want to do is have a record of how many unique items there are in a location (that is, a cell in the column is counted if it isn't blank and if its neighbouring cell in the other qualifying columns is blank). How do I go about this? As an example:
Here, the relevant columns are D to H, and rows 2 to 11. The last row should read 1, 1, 2, 2, 1 as there are duplicates in rows 2, 5 and 10.
It might also be useful to have a tally of how many items are duplicated in more than one location, which would obviously be one result rather than a result per column.
Thanks in advance!