How can I find clients that have bought three of a certain item and only those 3 items with excel formula

Question

I have a question similar to my last. I am trying to figure out how to find customers who have bought only tape, pen and stapler once and nothing else.

Name    CustmoerID  Item Name   Item #  Desired Results
Bob     1           Pen         555 
Bob     1           Stapler     222 
Bob     1           Stapler     222 
Bob     1           Tape        111 
Greg    2           Pen         555 
Greg    2           Pen         555 
Greg    2           Stapler     222 
Tim     3           Stapler     222 
Tim     3           Tape        666 
Tim     3           Glue        333 
Mark    4           Pen         555     Check
Mark    4           Stapler     222     Check
Mark    4           Tape        111     Check

This is the closet I think I got. But no luck.

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

Edit- This got me a bit closer but still not working properly.

=IF(AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3),"Check","")

@SkysLastChance Do they have to have purchased all three? Or just any one of those things no more than once? — jeffreyweir, Apr 20 '18 at 20:36
@SkysLastChance OK, I've added an answer in line with pnuts' suggestion. This is indeed what I would do. PivotTables are soo much better than Formulas when it comes to this stuff, IMHO. — jeffreyweir, Apr 20 '18 at 20:43
Whoops, just reread your requirement that you want all three. Please hold caller... — jeffreyweir, Apr 20 '18 at 20:43

score 2 · Accepted Answer · answered Apr 20 '18 at 20:51

2

This allows the use of two codes for the same thing and checks if there are three.

=IF(AND(COUNTIF(B:B,B2)=3,SUMPRODUCT(--(COUNTIFS(B:B,B2,D:D,{222,111,777,555})=1))=3),"Check","")

answered Apr 20 '18 at 20:51

Scott Craner

148,073
10
49
81

score 1 · Answer 2 · answered Apr 20 '18 at 19:26

You can use the following formula to determine if a given customer has order one and only one of a give item using the following formula (in this case for Pens):

=COUNTIFS(B:B, B2,D:D, 555)=1

You can then translate this to staplers and tape as well. The easiest way would be to put each of these into their own column and use an AND statement, but all together it would look like:

=AND(COUNTIFS(B:B, B2,D:D, 555)=1, COUNTIFS(B:B, B2,D:D, 222)=1, COUNTIFS(B:B, B2,D:D, 111)=1)

What if a item like a stapler had more then one code like 222 and 777? — SkysLastChance, Apr 20 '18 at 19:36

jeffreyweir · Answer 3 · 2018-05-02T22:46:18.003

0

If you wanted the folk that had purchased any one of these items, but not 1 of each of the three items, you could use a 'Traditional' PivotTable:

That's just a PivotTable with both an item filter applied (using a Slicer) and a Values Filter applied, to only show results where count = 1

Note that you need to check the Allow Multiple Filters Per Field option of the PivotTable Options>Totals & Filters dialog in order to set both a Filter and a Values Filter at the same time.

If you wanted folk who had purchased exactly one of each of the three items, you could either use a traditional PivotTable with a formula running down the side (which doesn't hold any advantages to simply using a formula like Scott Craner suggests with his answer), or you could use an OLAP PivotTable and write Measures using DAX. This fairly new capability is simply awesome: You can now write complicated formulas right within a PivotTable itself.

If using the PivotTable + Formula approach, it would look something like this:

...with the formula being:

=AND(GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",C$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",D$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",F$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3)=3)

But there's really no advantage between this and the great answer Scott Craner has given.

Or you can use DAX measures and an OLAP PivotTable based on the DataModel.

This requires you either to have a Excel that has the DataModel built in (i.e. Excel 2013 or later) or a version of Excel that has the PowerPivot add-in installed (Free for Excel 2010, built in to other Excel versions if you have the right premium version such as Professional Plus etc).

Here's the result:

...and here's the measures I used:

...and here's how to add a measure if you don't have PowerPivot installed (but have Excel 2013 or later): You simply right click here:

This requires you to check the "Add to Data Model" option when you first make your PivotTable:

UPDATE May 2018:

I posted a question on how to do this more dynamically at https://community.powerbi.com/t5/Desktop/Identify-customers-who-had-puchased-exactly-one-unit-of-three/td-p/407941 and got 4 great answers. All four work with Excel 365, but only the third answer from Phil Seamark worked in my build of Excel 2016. Here it is:

It works by using the old "parameter arg" trick of using 1, 2, 4, .... in a second column of Table2 so you could see if everything someone had purchased added to 7. Sneaky!

And here's the measure:

=COUNTROWS(
  FILTER(
    SUMMARIZE(
        'Table1',
        Table1[Customer],
        "Score", SUM('Table2'[ID]),
        "myRows",COUNTROWS('Table1')
   ),[Score]=SUM(Table2[ID]) && [myRows]=COUNTROWS(Table2))
 )

edited May 02 '18 at 22:46

answered Apr 20 '18 at 20:42

jeffreyweir

4,668
1
16
27

Ideally I would only want to see mark. Not the others. I do not see a way with the pivot table where I can do that. – SkysLastChance Apr 20 '18 at 20:45
Understood. What version of Excel do you have? – jeffreyweir Apr 20 '18 at 20:46
I use excel 2013. – SkysLastChance Apr 20 '18 at 21:02
Cool. You have something called the Data Model built in, meaning you can write measures even without PowerPIvot installed using the DAX language. This means it's likely you can use a PivotTable to do this. I'm struggling to put the right DAX measure together, but I'm sure I'll crack it and it is an interesting challenge that I might use in the book I'm writing. – jeffreyweir Apr 20 '18 at 21:05
Sweet! I appreciate the effort. If you do figure it out. I would love to see how. – SkysLastChance Apr 20 '18 at 21:07
@pnuts Good call. It's certainly simpler to run a formula column down the side of the PivotTable and use something like `=GETPIVOTDATA("Item Name",$F$1,"Name","Mark","Item #",111)+GETPIVOTDATA("Item Name",$F$1,"Name","Mark","Item #",222)+GETPIVOTDATA("Item Name",$F$1,"Name","Mark","Item #",555)=3` Only problem is, you'd need to check that the formula column gets copied down as far as the bottom row of the PivotTable. Nothing that a little VBA wouldn't accomplish. – jeffreyweir Apr 22 '18 at 21:37
Could put the formula in a table alongside the PivotTable, and use the code I posted at https://stackoverflow.com/questions/49339985/excel-extract-a-column-from-a-pivot-table-and-show-it-in-a-regular-table/49341702#49341702 to keep the table dimensions synced to the PivotTable dimensions. – jeffreyweir Apr 22 '18 at 21:41
Yeah, I tried to edit my comment but ran out of time. Am in process of editing my answer to include your much simpler approach. – jeffreyweir Apr 22 '18 at 21:53
Revised formula I just added to my answer accounts for that.Edit: Whoops, no it doesn't, but I'll make a change. – jeffreyweir Apr 22 '18 at 21:58
I'm curious whether there's a DAX formula that will let you do this *dynamically* i.e. where you don't have to hard-code the items into x different measures. Might post a question at a dedicated DAX/PowerBI forum just to learn whether it can be done. – jeffreyweir Apr 22 '18 at 21:59
@SkysLastChance New formula added that does this all dynamically in one measure. Check the edit and the link. Thanks for the learning opportunity. – jeffreyweir May 02 '18 at 22:47

How can I find clients that have bought three of a certain item and only those 3 items with excel formula

3 Answers3