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

Question

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

Lissette Roman 0

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.

0 comments

2 answers

Your answer

Answer 1

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)")))
)

Answer 2

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 column User'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.