Conditional Formatting Alternative for duplicate values?

[mattxu2006]

Hello Gentlepeople
occasionally I want a deeper view into duplicate values.
I want to see not only what is duplicate, yes or no, but also which duplicates tie out.

I apply the conditional format for unique values to make the cells look normal and then the next rule is to apply the color scale to the same range. This gives all the duplicates with the same value the same color while differentiating between duplicates.

Is there an another way to do this with fewer steps?

Thanks
Matt

 

[bugmonsta]

Just thought I would also pop in a solution I had from a few years back for finding duplicate rows. In the example below, my duplicates were in column A

=IF(COUNTIF($A$1:$A2,A2)>1,"I'm a duplicate with row A"&MATCH(A2,$A$2:A2,0),"Original")

It will mark the first "find" as original and any others as a duplicate (with the row number of the first match)

[table="width: 500, class: grid"]
[tr]
[td]Apples[/td]
[td]Original[/td]
[/tr]
[tr]
[td]Apples[/td]
[td]I'm a duplicate with row A1
[/td]
[/tr]
[tr]
[td]Pear[/td]
[td]Original[/td]
[/tr]
[tr]
[td]Apples[/td]
[td]I'm a duplicate with row A1
[/td]
[/tr]
[tr]
[td]Pear[/td]
[td]I'm a duplicate with row A2
[/td]
[/tr]
[tr]
[td]Apples[/td]
[td]I'm a duplicate with row A1
[/td]
[/tr]
[/table]

 

[mattxu2006]

Thanks bugmonsta,
While this does pretty much do what I asked I am looking to reduce steps.

I also want to keep color coding.

 

[bugmonsta]

Hi Again,

Have you had a look at this post on Mr Excel - JLGWhiz created some code that has 20 different colours in it that may work for you?

 

[mattxu2006]

Thanks for the link.
the macro n the link is something to build upon.