CODEWITHSUNDEEP
excelexcel-2013
SkysLastChance
SkysLastChance

Reputation: 221

Trying to find clients that have ONLY bought one certain item in excel

I have two items codes 555 and 777 that are the same item (Pen). If they are the only items a customer has bought I would like to see just them. Example below

Name    CustomerID  Item Name   Item #  Desired Result

Bob              1  Tape           111  
Bob              1  Tape           111  
Bob              1  Pen            555  
Greg             3  Pen            555    Check
Jim              4  Tape           111  
Jim              4  Pen            555  
Tom              7  Tape           111  
Tom              7  Stapler        222  
Jack             8  Pen            777    Check
Zach             9  Pen            555  
Zach             9  Paper          333  
Zach             9  Stapler        222  
Zach             9  Tape           111

=IF(OR(AND(B1:B3,D2=555),AND(B1:B3,B2=777)),"Check","")

is what I have tried but it just marks any with 555 or 777.

Upvotes: 0

Views: 314

Answers (2)

Scott Craner
Scott Craner

Reputation: 152605

use:

=IF(AND(OR(D2={555,777}),COUNTIF(B:B,B2)=1),"Check","")

enter image description here

Upvotes: 2

Elijah Moreau-Arnott
Elijah Moreau-Arnott

Reputation: 381

If you know that the customers are sorted, you could try something like: =IF(AND(OR(D2=555,D2=777),AND(B2<>B1,B2<>B3)),"Check","")

unless you also want to check customers who bought both 555 and 777. AND(B1:B3,D2=555) probably isn't doing what you want. B1:B3 will always be 'true' (it's not an expression, its just a range), so that is only checking if D2=555

Upvotes: 0

Related Questions