Forum Discussion
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...
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<>"")
To handle that case, select the range A1:A8 and make a New Rule for Conditional Formatting and set the format as per your choice.
=SUMPRODUCT(--($A$1:$A$8=A1))>1