Forum Discussion

MGagnier's avatar
MGagnier
Copper Contributor
Jan 07, 2020
Solved

Excel conditional formatting for duplicate values

I'm using conditional formatting in a column to highlight duplicate values (part numbers). The Number format in this column is set to "Text".   My problem is that we have part numbers that start by...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 07, 2020

    MGagnier 

    I don't know for sure, but my guess is that conditional formatting is using here same algorithm as COUNTIF(), i.e. converts texts which could be converted to numbers to them and perform number comparison.

     

    The workaround is the same - use rule with the formula, with SUMPRODUCT() or like COUNTIF(<range>, <value>&"*").

     

    E.g if part numbers in column E the rule formula could be

    =(COUNTIF($E:$E,E1&"*")-1)*(E1<>"")
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 07, 2020

MGagnier 

I don't know for sure, but my guess is that conditional formatting is using here same algorithm as COUNTIF(), i.e. converts texts which could be converted to numbers to them and perform number comparison.

 

The workaround is the same - use rule with the formula, with SUMPRODUCT() or like COUNTIF(<range>, <value>&"*").

 

E.g if part numbers in column E the rule formula could be

=(COUNTIF($E:$E,E1&"*")-1)*(E1<>"")

Resources