CODEWITHSUNDEEP
excel
Morallis
Morallis

Reputation: 151

Excel: View clients who don't have a product

I have a table of clients and the products that they have purchased. I'm looking for a simple way of being able to filter to view all clients who don't have a certain product.

Client | Product
------ | ------
John | A
John | B
John | C
Kate | A
Kate | B
Kate | D
Mary | A
Mary | D

With the above example I would want to look for which clients do not have Product -> C, the return I'm after is Kate and Mary.

I've tried looking at this in a few different ways but I feel I'm over complicating it. I was creating a table to return who has the product then doing a lookup from there against another table of all users to then find out who wasn't in the first list.

I tried using a pivot table to get what I was after but I'm only able to return who has the products rather than who doesn't, also filtering product C from the pivot table does not help as the Client still shows up having other products.

I'm hoping there is an easier way to do this.

Your assistance is appreciated.
Dane

Upvotes: 0

Views: 150

Answers (1)

hsan
hsan

Reputation: 1559

COUNTIFS should to the trick here.

You have one cell where you enter the product to look for. Then you add a column to your table that checks if the client does not have that product.

=COUNTIFS([Client],[@Client],[Product],referenceToTheProductToLookFor)=0

This will count the rows where

  • the entry in the column "Client" is the same as the value in the column "client" in the current row ([Client] references the whole column, [@Client] only the current row's value of that column)
  • the entry in the column "Product" is the same as the one entered in your input cell

and checks if the resulting count is 0. If it is 0 the cell value will be TRUE, otherwise it will be FALSE.

If you want to avoid having to make two steps each time you change the product you are looking for (1. enter the product, 2. update the filter on the table) you could use the worksheet's On_Change macro to detect changes to the product code and then automatically update the filter on the table.

Upvotes: 1

Related Questions