How to filter out any client with specific products in their account

Lissette Roman 0 Reputation points
2026-10-09T00:32:26.5766667+00:00

I have an Excel spreadsheet with a list of clients and products as well as account representatives assigned for each client. I need to identify all clients whose combination of products include specific products within that combination and identify clients that do not have specified products at all. A pivot table is not allowing this.

Ex. our customer account rep, customer name, customer product A, B, C, D,etc.

Need to filter out clients that show any of product D & E, even if they have product A, B, C.

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer recommended by moderator
Lissette Roman 0 Reputation points
2026-10-14T02:10:24.2133333+00:00

I ended up using this after I color coded 2 sets of customers for specified products and this worked:
Removing All Rows for Clients Appearing in Orange Goal:

If a client appears in an orange row, delete both the orange row and any matching green row(s).

  1. Filter the spreadsheet by the orange fill color.
  2. Copy the client names from the orange rows into a separate column, such as Column Z.
  3. Add a new helper column on the main sheet and name it Delete.
  4. In the first data row of the helper column, enter: =IF(COUNTIF($Z:$Z,A2)>0,"DELETE","")
  5. Adjust A2 if your client names are in a different column.
  6. Copy the formula down for all rows.
  7. Any row belonging to a client that appeared in the orange list will display DELETE.
  8. Filter the helper column for DELETE.
  9. Select all visible rows and choose Delete Sheet Rows. Example Formula =IF(COUNTIF($Z:$Z,A2)>0,"DELETE","")

Was this answer helpful?

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102.6K Reputation points Volunteer Moderator
    2026-10-09T22:59:26.18+00:00

    Hi,

    Create a Pivot Table and drag Customer to the row labels. Create a slicer of Product and select D and E. Drag customer to the value area section.

    Was this answer helpful?

    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2026-10-09T05:32:57.92+00:00

    Sample file:
    https://www.dropbox.com/scl/fi/654up6x75lihwsa7b6653/6031029.xlsx?rlkey=ylfn0jl1wxjx4zz4uz5wdxjq0&dl=1

    grafik

    E2:

    =SORT(UNIQUE( FILTER(B2:B21,(C2:C21="D")+(C2:C21="E"),"(none)")))

    G2:

    =LET(
    DorE, FILTER(B2:B21, (C2:C21="D") + (C2:C21="E")),
   SORT(UNIQUE(FILTER(B2:B21, ISNA(MATCH(B2:B21, DorE, 0)), "(none)")))
)

    I2:

    =LET(
    D, FILTER(B2:B21, C2:C21="D"),
    E, FILTER(B2:B21, C2:C21="E"),
    SORT(UNIQUE(FILTER(D, ISNUMBER(MATCH(D, E, 0)), "(none)")))
)

    K2:

    =LET(
    D, FILTER(B2:B21, C2:C21="D"),
    E, FILTER(B2:B21, C2:C21="E"),
    D_und_E, FILTER(D, ISNUMBER(MATCH(D, E, 0)), ""),
    SORT(UNIQUE(FILTER(B2:B21, ISNA(MATCH(B2:B21, D_und_E, 0)), "(none)")))
)

    Was this answer helpful?

    0 comments
  3. Teddie Dang 1,705 Reputation points Independent Advisor
    2026-10-09T01:55:18.9566667+00:00

    Hi @Lissette Roman

    The approach will depend on how the data is structured. Here are two examples:

    1.One row per client, with each product in a separate columnUser's image

    To identify clients that have either Product D or Product E:

    =IF(COUNTIF(F2:G2,"Yes")>0,"Exclude","Keep")

    To identify clients that have both Product D and Product E:

    =IF(COUNTIF(F2:G2,"Yes")=2,"Exclude","Keep")

    User's image

    2.One row per product

    User's image

    In F2, create a unique customer list:

    =UNIQUE(B2:B1000)

    Then, use the following formulas according to your requirement.

    -Customers with both Product D and Product E

    =AND(COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"D")>0,COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"E")>0)

    -Customers with Product D or Product E

    =OR(COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"D")>0,COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"E")>0)

    -Customers with neither Product D nor Product E

    =AND(COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"D")=0,COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"E")=0)

    User's image

    I hope this helps.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.