Need formula to Detect "Different products" for same ID IN EXCEL

I’ll need a formula in excel to detect for example 1111; has three different products linked, meanwhile 2222 has only linked "oranges"

Here I left and example of my excel table:

Product ID Product Name
1111 Apples
1111 Orange
1111 Grapes
2222 Orange
3333 Apples
3333 Orange
3333 Grapes
4444 Orange

Hope it is clear!

Thanks in advance.

>Solution :

Sharing just for clarity, refer image below,

• Formula used in cell C2


• Formula used in cell D2 –> You may use this as well or modify to make it look bit fancy

="Product ID "&A2&"-Product Type No.-"&COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1


Leave a Reply