Show duplicates

Occasional Contributor

Hi I want to show duplicates, ideally a unique colour for each machine so that it can be visually identified.



Else is there a way to quickly move between duplicates?


Thank you!

1 Reply

@kaihendry  you could always go back to the original table.  But as a possible help to you here is an idea:

you could create a column on  the orig table with this formula:

=IF(COUNTIF($H:$H,$H9)>1, MATCH($H9,$H:$H,0),"")

That will create a unique value for each duplicate machine (i.e. row of first occurrence) 

Then include that column in the pivot table and you can use the graded color scale to 'link' duplicates (based on cell value -> 3-color scale) with a unique color.  Now the "unique" colors are technically infinite but in reality you probably will have trouble after a half a dozen unique names.

A more 'brute force' method could help in that instead of a single conditional formatting rule you can create dozens of custom rules.  The advantages include you can highlight more than that single cell and you have complete control over the color selection.  The disadvantage is that you have to create each rule and the number of rules you create is the limit to how many get highlighted.  A 'generic' conditional formatting formula would be: 


assuming those unique values are in col B and then change the "1" in the "SMALL" function to 2, 3, ... for each additional unique conditional formatting you need to add and each conditional formatting you define the unique color to use.

NOTE: if you apply this to a range like A:B or A:D or what ever, you will highlight all the cells in that same row.


Hope that helps.