highlight duplicates over a certain length

Hi,

I'm trying to highlight duplicates over a certain length.. I know you can remove duplicates through the conditional formatting menu but I need to enter a formula that limits it to duplicates that have over 12 characters as duplicates less than this character length i do not want to highlight.

I believe it should be something along the lines of len(A1) > 12 and is duplicate then apply chosen highlighting.

I am not sure how to code the 'and is duplicate' though. Any help would be appreciated.

Note - I did try to filter the column first by character length and then apply the formatting but this unsurprisingly doesn't work.

Thanks,

Rob

Try

=IF(AND(LEN(A2)>12,COUNTIF($A$1:A2,A2)>1),"D","")

Filter on "D" for duplicates with length > 12

Hi thanks for the response.. I have just tried to test the provided formula by pasting into the 'new rule formula window' formatted with orange highlighting but no highlighting has been applied.

Thanks,

Rob.

The formula was intended to put "D" in a column where duplicates existed: you can then filter on the column rather than use CF.

Try in CF

=AND(LEN($A1)>12,COUNTIF($A$1:$A$1000,$A1)>1)

Hi John,

Thanks very much.. that works. I have just pasted it in for now, but will look into the combination of IF/AND to understand it more.

Thanks again.

Rob.