+ Reply to Thread
Results 1 to 10 of 10

Conditional Format based on multiple criteria

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Conditional Format based on multiple criteria

    I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
    Attached Files Attached Files
    Last edited by NBVC; 02-16-2010 at 10:24 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Format based on multiple criteria

    What are the actual conditions you want to check?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Conditional Format based on multiple criteria

    they are in the attached file in the 2nd sheet. or they should be.
    i am just checking the conditions that these records have certain fields which are equal to each other and to highlight them accordingly.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Format based on multiple criteria

    Original and override prices do not match in those hightlighted records..do they?

  5. #5
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Conditional Format based on multiple criteria

    i figured i would forget to mention something.
    they do not match in the same record, however they do match in the two separate records. in other words, the one on the bottom matches the one on the top and that is the problem that im running into.
    i need to highlight the two records when they match on that field even if they are not on the same line.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Format based on multiple criteria

    Select Row2 to bottom row

    Go to Home menu: Conditional Formatting: Manage Rules

    Click New Rule.. and select use formula to determine which cells to format

    Enter Formula: =AND($A2<>"",$F2=$F3,$G2:$G3,$H2=$H3,$L2=$K3)

    Click Format and choose from the Pattern Tab

    Click Ok

    Click New Rule.. and select use formula to determine which cells to format

    Enter Formula: =AND($A2<>"",$F4=$F3,$G3:$G4,$H4=$H3,$K4=$L3)

    Click Format and repeat as above..

  7. #7
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Conditional Format based on multiple criteria

    Thanks, that worked great. Only one problem: the formula is highlighting one of the matching records and then the record above the other matching record; instead of both matching records.
    im trying to decipher your formula so i can fix it, but im not the best in this arena..

  8. #8
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Conditional Format based on multiple criteria

    should that be =AND($A2<>"",$F2=$F3,$G2=$G3,$H2=$H3,$L2=$K3)
    rather than $G2:$G3 ??
    just seemed like the odd man out.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Format based on multiple criteria

    Scratch that...

    Do the first part with rows 2 to bottom selected and apply the formatting...

    Then select rows 3 down and paste the same formula in:

    i.e. use: =AND($A2<>"",$F2=$F3,$G2=$G3,$H2=$H3,$L2=$K3) for rows 2 down and then again for rows 3 down
    Last edited by NBVC; 02-16-2010 at 10:15 PM. Reason: noticed a colon where a equal sign should be

  10. #10
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Conditional Format based on multiple criteria

    there we go, that is working great. this should save a lot of time.
    i may have another question but its getting late and im about to head home so maybe i will post it in this thread tomorrow if i dont figure it out myself. Thanks a million.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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