+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting, Duplicates BUT with conditions REOPENED

  1. #1
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    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.
    Attached Files Attached Files
    Last edited by TSACov; 08-30-2020 at 03:28 PM.
    Thanks!
    - TSACov

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional Formatting, Duplicates BUT with conditions

    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.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    Re: Conditional Formatting, Duplicates BUT with conditions

    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

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional Formatting, Duplicates BUT with conditions

    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.

  5. #5
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    Re: Conditional Formatting, Duplicates BUT with conditions

    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.
    Attached Files Attached Files
    Last edited by TSACov; 08-30-2020 at 01:54 PM.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional Formatting, Duplicates BUT with conditions REOPENED

    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: copy to clipboard
    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: copy to clipboard
    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.

  7. #7
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    Re: Conditional Formatting, Duplicates BUT with conditions REOPENED

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

    Thank you so much!!

    TSACov

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional Formatting, Duplicates BUT with conditions REOPENED

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting using multiple conditions to find duplicates
    By TitansGo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-28-2016, 11:26 AM
  2. Delete Conditional Formatting conditions but keep cell formatting
    By Devendra.dvm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2016, 04:07 AM
  3. Conditional Formatting for Duplicates
    By endly in forum Excel General
    Replies: 12
    Last Post: 05-30-2015, 10:05 AM
  4. Delete Conditional Formatting conditions but keep cell formatting - Excel 2010
    By tetreama in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2012, 08:28 PM
  5. [SOLVED] Three-way Conditional Formatting for Duplicates?
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-19-2011, 05:34 AM
  6. Duplicates Conditional Formatting
    By jayclinton in forum Excel General
    Replies: 4
    Last Post: 12-20-2010, 07:41 PM
  7. conditional formatting for duplicates
    By zachg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-04-2007, 04:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1