Conditional Formatting, Duplicates BUT with conditions REOPENED

Hi Team,

I have an issue with my conditional formatting. I have a standard duplicate format and I also have a conditional formatting formula that STOPS that duplicate formatting if a condition is met.

E.G. Column B lists locations. If the location is repeated it will format it yellow as it is a duplicate. However, should the cell adjacent in column E have either 'Verbal Negative' or 'Non-Escalation' it will stop the duplicate process which is great and is what I need!

However, it also stops the other cells being highlighted if the adjacent cell in column E contains "911" or "Escalation" which is not what I need. I need to the duplicate formatting to reapply itself to these circumstances.

E.G

B -------------------------- E
C12------------------------ Verbal Negative
C12------------------------ Escalation
C12------------------------ 911
C12------------------------ Non-Escalation

So B1 and B4 should not be highlighted, but B2 and B3 should be.

Please see attached what I mean. Im probably making it more complicated than it needs to be.

I can't open your file for some reason, but maybe I can help anyway.
Try this on a copy of your file, obviously, just in case - that's always good practice.

First, clear the Conditional Formatting (CF) rules you have - both the duplicates one and the Verbal Neg/Non-Esc one - so that we're starting afresh.
Now select column B (or your range within col B) and apply the standard 'Duplicate Values' CF.
Make a note of what formatting you choose - the default uses both Fill and Font formatting.

With column B (or your range within it) still selected, go to CF, choose 'New Rule' and 'Use a formula to determine which cells to format'.
Enter this as the formula:
=OR($E1="Verbal Negative",$E1="Non-Escalation")
If you've selected a range within col B, then change $E1 to the appropriate row - for example if your data starts on row 5, use $E5.
Click the 'Format' button.
If you've used the default duplicates formatting, you now need to take off the 'Fill' and 'Font' formatting it applies. So go to the 'Fill' tab and select 'No Color'. It will look like it's already selected, but click it anyway - you should see the 'Clear' button in the bottom-right turn from gray to black. Now go to the 'Font' tab and under 'Color' re-select 'Automatic' - again, the 'Clear' button should turn black.
If you customised the formatting for duplicates with anything other than Fill or the colour of the Font, then do the same sort of thing - re-select the default setting.
Now OK/Apply to get back to the sheet. It should now show what you want.

Note that if you have other formatting applied, either using CF or manually, this second step will clear that as well.
Manual formatting you'll have to do again manually (I know that sounds weird, sorry).
To have any other CF still be applied, go to CF then 'Rules Manager' and adjust the order of the rules. Excel applies the rules from the bottom up.

I hope that helps.

Hi Aardigspook!

I got a little lost at this point:

"So go to the 'Fill' tab and select 'No Color'. It will look like it's already selected, but click it anyway - you should see the 'Clear' button in the bottom-right turn from gray to black. Now go to the 'Font' tab and under 'Color' re-select 'Automatic' - again, the 'Clear' button should turn black."

But I got around it with selecting the 'stop if true' function for the formula you suggested and it worked great! So problem solved!

Thank you!

TSACov

Sorry if I wasn't completely clear. I should have written "the colour of the word 'Clear' on the button should change from gray to black" - it's not the button itself which changes colour. Also I was testing it on Office 365 at work which is based on Office 2016, so your version might look/work slightly different/ly.

However, since you've managed to get it working yourself, that's great. Glad that I could help a bit. Thanks for the rep and for marking the thread as Solved.

Hi. Super sorry to do this, but I am reopening this thread (apologies if Im not allowed to do that, I am actually unsure!)
I have attached another copy of my excel. Hopefully you will be able to open it.

At this point, the duplicate formula and the conditions work. However, if I have the below example:

B -------------------------- E
C12------------------------ Verbal Negative
C12------------------------ Escalation
C12------------------------ Non-Escalation
C12------------------------ Non-Escalation

Technically according to my rule as what counts as a duplicate (a location that has 2 or more occurrences in column B and has the action of escalation or 911 in column E), this would mean that none of the the cells in B should qualify of the duplicates as only one cell would qualify, which is the C12-----Escalation. At the moment with the current set of formulas/conditional formatting, the above example where C12 has an 'Escalation' is still highlighted yellow. Does that make sense?

So i only want the duplicate conditional formatting to apply when the conditions mentioned previously are met. Another way to put it is that if I was to put another C12 in column B and in E as an escalation or 911, only THEN would that previous C12 in column B be highlighted.

Sorry for the confusion.

There's no problem with re-opening your own question, so don't worry about that.

I think the easiest way to do what you want is to use a helper column and then use that in the CF formula. Choose a column away from your data (col S, for example). Title it 'Helper' or similar, just to help you remember what it's for. In S2, put this:

Formula:

Please Login or Register  to view this content.

Drag that down to row 61 (level with the bottom of your table). With the data you have in the example, you should see a whole column of 'n/a' except for in S3, which should read 'C12-highlight'.

Now select B2:B61, then go to your CF rules (CF, 'Manage Rules'). You can get rid of the one with the formula I suggested above. Also delete the Duplicate Values rule.
Now click 'New Rule' and 'Use a formula to determine which cells to format'. Enter this formula:

Formula:

Please Login or Register  to view this content.

Choose the format you want (yellow fill, for example).

With this done, so far nothing is highlighted in col B. When I change E4 to 911, both B3 and B4 highlight, because they're the same in col B with 'Escalation' and '911' in col E. So I think it's doing what you want.

Attached is your file with this working - on a copy of your example sheet. Hope that helps.

Now THAT worked! Absolutely perfect. Thank you once again, if I could add reputation twice I would.

Thank you so much!!

TSACov

You're welcome. Glad to be of help.
(And don't worry about the rep - it's not why I do this!)