Find number that is exclusive to customer

[ALAMZEB]

Hi Guys



Is there any formula that figures out if part number is exclusive to customer. The list goes to 20,000 rows

For example, in attached table “Part number 4” is exclusive/sell only to Customer A so formula will tell that “4” is sold exclusive to Customer A

the purpose is to find out whicpart numbr have only one customer/buyer

thanks in advance guys

 

[John Jairo V]

Hi @ALAMZEB

Try this formulae (in G5 and drag it down):
=IF(SUMPRODUCT(N(($F$5:$F$17<>F5)*$E$5:$E$17=E5)),"",E5&" "&F5)

Blessings!

 

[ALAMZEB]

H John

Part number are mixture of alphabets & numeric. Applying above formul returns error

 

[John Jairo V]

Part number are mixture of alphabets & numeric. Applying above formul returns error

Your example must be representative of your real data. Please put the cells into an example that gives you an error. Blessings!

 

[p45cal]

In G5, copied down:
=SUMPRODUCT(($E$5:$E$18=E5)*($F$5:$F$18<>F5))
will return a zero where exclusivity occurs.
You can use this within a longer formula:
=IF(SUMPRODUCT(($E$5:$E$18=E5)*($F$5:$F$18<>F5))=0,"Part no. " & E5 & " is exclusive to " & F5,"")
However both these formulae will return multiple rows for the same exclusive customer for a part (eg. if there were several Part no. 1 for Customer B).
You could of course Advanced Filter this for uniques, but far quicker is a pivot table, filtering Part number by Value on Count of customer name being = to 1: