Finding all parts that were only purchased by one customer

Hello All,

For some reason (maybe because it's Monday), this one has got me stumped.

I have a spreadsheet with invoice history from 1/1/2015-1/31/2016. I am trying to find a way to identify the parts that were purchased by only one customer (doesn't matter if they purchased multiple times within a month/year, just has to be that the part was purchased by only one person).

If you see the example below, I only want to return part # ABC2 because Will is the only customer to purchase the product.

Part # Customer Date
ABC1 Kate 1/15/2015
ABC2 Will 2/15/2015
ABC2 Will 2/15/2015
ABC1 Will 3/15/2015
ABC1 John 3/15/2015
ABC1 Sarah 4/15/2015
ABC2 Will 5/15/2015


The Pivot Table isn't working because if I use the count feature it simply counts the number of times the customer shows up for that part, so I don't have a way of filtering that easily.

I'm not sure if this makes sense, but hopefully someone out there has an idea of what to do!

Thanks.

Assuming your data in A to C


Helper column E

in E2 and copy down

=IF(IF(COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)=COUNTIF($A$2:$A$15,$A2),1,0)<>0,COUNTIFS($A$2:A2,A2,$B$2:$B2,B2),0)

in F2

=IFERROR(INDEX($A$2:$A$15,SMALL(IF($E$2:$E$15=1,ROW($A$2:$A$15)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

Thanks so much! That seemed to do the trick. I'm trying to follow the formula you used in column E. Can you explain briefly what exactly this formula looks at?

It compares the COUNTS of Parts+Customer combination against Name so we find the count of ABC2 and Will combination =3 and the COUNT of Will =3 therefore Will is the only purchaser. If this match is found, the result is 1, otherwise zero.

For each ABC2/Will match, 1 is (effectively) put in column E: this the inner IF ....

IF(COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)=COUNTIF($A$2:$A$15,$A2),1,0)

The Outer IF

=IF(IF(.............) <>0,COUNTIFS($A$2:A2,A2,$B$2:$B2,B2),0)

counts the 1s for the combination of part and name. As the formula moves down the rows, the counts for ABC2/Will are 1, then 2 then 3

The formula in F2 downwards finds all the 1 counts i.e. first (or only) occurrence of a valid pair and returns the part number.

You separate these out into 3 columns

in E2

=IF(COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)=COUNTIF($A$2:$A$15,$A2),1,0) will give list of 1s and 0s

in F2

=IF(E2<>0,COUNTIFS($A$2:A2,A2,$B$2:$B2,B2),0) will count (sum) 1s for a give pairing

in G2

=IFERROR(INDEX($A$2:$A$15,SMALL(IF($F$2:$F$15=1,ROW($A$2:$A$15)-ROW($A$2)+1,""),ROWS($A$2:A2))),"") will retrieve Part

Hope this helps