Highlight unique values for easy visual purposes

Hi all,

in my data set, I would like my values to have conditional formatting applied so I can easily identify duplicate sequences.


So in my example.., B3 - TO G12

I want all the unique values to be highlighted in any unique colours.

Example: 10001 can be in green 100013 can be be in blue, 10002 can be in light pink etc.

Is there a way to achieve this please?

thank you!

Please explain in more detail: what constitutes 'unique' here? I ask becuse 10001 occurs multiple times.

Every instance of 10001 to be colour A, every instance of 10002 to be colour B, etc??? If so, that'll need VBA.

Hi all,

Thank you for your replies

I think Glenn got what I was after

Every instance of 10001 to be a colour
Every instance of 10002 to be a colour

but I would want to apply the formatting/rule/process to other spreadsheets so I`m unsure if this is possible? sometimes I might apply the process to words in a list (same format as the way the numbers are)

Thank you

It doesn't matter if they're text or numbers.... without using VBA you would need to set up (as Pete said) one CF formula for EACH unique value. In your sample, that's 32 formulae. It's not really practical, is it??

ok no worries, thank you guys. I appreciate your guidance on how I need to go about achieving what I need :-)