Using conditional formatting to highlight multiple duplicates

Hi,

I am trying to find a way of highlighting multiple duplicate entries in a row of data. I inititally used conditional formatting to highlight duplicates but the problem was I only wanted to highlight duplicate entries which have been entered 3 or more times. The following example sheet should illustrate this more clearly.

As shown in the sheet code A26 would be highlighted red as it occures 5 times. Code B7 ideally would be highlighted blue as it occurs 3 times but is a different code, although it seems that it may not be possible to do this.

Hopefully somebody can help me out!

Try this

Condition 1 formula

Please Login or Register  to view this content.

Condition 2 formula

Please Login or Register  to view this content.

Condition 3 formula

Please Login or Register  to view this content.

See the attached worksheet for the proof

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

Thanks a lot that works great...

Although I have one more slight problem, the codes that I gave will not always be the same. Basically the codes could be any ones from a list of say 15, and I need to flag the duplicates of whichever ones happen to appear on a given day.

I have included another example sheet, the codes could be any from those in column O and will always differ. so instead of condition one code being:

Please Login or Register  to view this content.

can "C1" instead reference the list in column '0' rather than a specific code.

hmmm..........

I'll give it a try, but cross your fingers for the formula cavalry ... to arrive...

The problem we have here is that 2003 only allows 3 conditions in Conditional formatting.

2007 can handle the 13 that you need by using the formulae

Please Login or Register  to view this content.

Where your lookup data is in $O$14:$O$26

However 2003 will only take the first 3

There are ways to overcome this limit using VBa but they will be quite complicated in this case.


I have attached 2 workbooks, one in 2007
and the same one saved as 2003 to let you see what happens

Change the Values in row 3 to test.

Having said that most people get quite visually confused when the are more than 3 colours to evaluate.
It might be better all round to try some other method to highlight your problem.

Hope this clears things a little.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

Would you be interested in a macro?

A macro would be fine! Although it would have to be one that is run on cell entry to avoid having to click a button or something all the time...

Hi Jonny,

Try this sheet and see where we go from here.

Select any code from the Drop-Down Lists in B3:Z3

The code only works in row 3 at the moment, until I know a bit more about your sheet structure.

I have added a Lookup sheet to handle the Drop-Downs via a named range, this can be made dynamic later, and the code adjusted to accomodate this.

You might also find the sheet Palettes handy when changing the colours in the code.

This code effectively gives you up to 56 conditional formats with 2003 on fonts alone.
I'll let you workout the number of conditions you can have by combining font, itallic,bold, size, etc, then add background .........

At the moment it handles 13 on your code list.

The colours become a bit visually confusing I think you will agree.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

Thanks a lot, that looks good!

I have to say that I dont entirely understand the code, so I was wondering is there any easy way that it can be adjusted so that rather than changing colour depending on fail code, the colour changes depending on the quantity of occurance of each code?

Im not sure which way to go about highlighting the cells yet so I want to look at both ways and then decide!

Thanks.

Almost anything is possible using VBa!

We just need to know the rules to apply.

Let's know what you decide.

I think that looking at it a bit further I would be better to have it set up so that the codes are highlighted with a colour according to how many times they occur. I have changed the sheet slightly to include three rows and try and show what I am trying to do.

I'll try to make this the last complicated question!!

Thanks.

Hi Johnny you can do what you are after in 2003 using conditional formatting.

1/. First format all text in your table green as standard,

Then you only have three conditions left.

Please Login or Register  to view this content.

I have updated the attached file to suit and explain

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

Thats great, thanks a lot!!