excelexcel-formulaformuladifferenceexcel-tables

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
Solution

  • Sharing just for clarity, refer image below,

    • Formula used in cell C2

    =COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1

    • 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

    FORMULA_SOLUTION