Conditional format - Different colour's for different duplicates

[Beau the dog]

Good afternoon,

Simple explanation, I have a list of postcodes in column F, starting at F6. I would like to highlight the duplicates, but a different colour for each different duplicate.

Without VBA please? I'm sure this is possible, just not for my tiny excel brain! I'm thinking a helper column is needed to give these different duplicates a reference number to conditional format against? But that formula is where I'm stuck...

 

[Fluff]

How many different duplicates are you likely to have?

 

[Beau the dog]

How many different duplicates are you likely to have?

I would say no more than 10...

 

[Fluff]

Ok, you could use this formula in B2

=UNIQUE(FILTER(A2:A1000,(A2:A1000<>"")*(COUNTIFS(A2:A1000,A2:A1000)>1)))

and for the CF use

=AND(B$2<>"",ISNUMBER(MATCH(A2,B$2,0)))

Then just change the B$2 to B$3 etc

 

[Beau the dog]

Ok, you could use this formula in B2

=UNIQUE(FILTER(A2:A1000,(A2:A1000<>"")*(COUNTIFS(A2:A1000,A2:A1000)>1)))

and for the CF use

=AND(B$2<>"",ISNUMBER(MATCH(A2,B$2,0)))

Then just change the B$2 to B$3 etc

Spot on, thank you once again.

 

[Fluff]

You're welcome & thanks for the feedback.