Forum Discussion
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 a zero, which causes the conditional formatting rule to incorrectly identify different part numbers as duplicates but are not.
Per example (see attached file), we have part numbers "7023" and "07023" which are identified as duplicates. I'm assuming that the rule ignore zeros at the beginning when looking for duplicates. I know the rule is "working" since it's correctly identify part number "23456" which is two times in the list.
Is there a way to refine the settings of the conditional formatting (or for what is a duplicate??) to consider the zero at the beginning as part of the values to analyse? I want to avoid to use a formula for this.
Thanks.
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<>"")
3 Replies
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<>"")Thanks, it works!
To Subodh_Tiwari_sktneer, your solution works too, but in my sample file I forgot that the range I've used in my "real" file was for the complete column ($A:$A), and when I've applied your formula all the blank cell had then got the formatting...
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
