+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting: Comparing Two Sets of Data, Format if no match

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 Apps for Business (v 2306 / 16.0) 64-bit
    Posts
    27

    Conditional Formatting: Comparing Two Sets of Data, Format if no match

    I have two sets of data, one set that updates continuously, and one that is much more static and is basically used as a reference. They are located on the same sheet.

    'Data Set # 1'
    Column B contains user-input client codes and column F contains user-input client percentages.

    'Data Set # 2' The reference set
    Column M contains unique, static (not updated often) client codes. A client code cannot be entered into Column B if it's not showing in this list in column M... Each corresponding percentage for each client is listed in column P. So basically, each client has an assigned percentage.

    So the Client code in M and the percentage in P are the source/reference information, so it may state that Client: AAA000 @ 20% and BBB001 @ 19%.

    What I need to do it place conditional formatting in column B, where if a user enters an improper percentage for a specific client (so user enters in Column B the AAA000 client with the wrong percentage in column F, such as 10%) it will highlight the client code.

    Each client code is assigned a specific percentage, so there should always be a corresponding percent that goes in column F depending on which code is entered.

    BUT WAIT, THERE'S MORE!
    The conditional format should also ignore blank percentages in column F. If a percentage is not entered, then it should not be highlighted.

    I CANNOT USE DATA VALIDATION as much of this input may or may not be automatically entered using a VBA macro, and VBA macros do not follow validation rules, so I am required to do it this way.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Conditional Formatting: Comparing Two Sets of Data, Format if no match

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.



    A clean sample is much more useful for us and you than a wall of text in the forum.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 Apps for Business (v 2306 / 16.0) 64-bit
    Posts
    27

    Post Re: Conditional Formatting: Comparing Two Sets of Data, Format if no match

    Here is a sample copy of what I am trying to accomplish, including some notations as well
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-26-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 Apps for Business (v 2306 / 16.0) 64-bit
    Posts
    27

    Re: Conditional Formatting: Comparing Two Sets of Data, Format if no match

    Bump! .

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Conditional Formatting: Comparing Two Sets of Data, Format if no match

    How many rows of data will the real worksheet have?

    I think I solved it by selecting column B then going to conditional formatting and using the following formula.
    Please Login or Register  to view this content.
    Keep in mind all you need is a formula where the formatting is applied IF true. So if rate is BLANK then FALSE, otherwise if the vlookup is different than the reference than highlight it.
    See Attached for the solution as well.



    This formula will fail if there is a unique client in column B but NOT in column M.
    Attached Files Attached Files
    Last edited by mikeTRON; 09-06-2016 at 05:08 PM.

+ 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. Conditional formatting looking at two data sets
    By seraph001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2016, 04:59 PM
  2. Replies: 6
    Last Post: 10-29-2015, 02:35 PM
  3. Replies: 0
    Last Post: 09-25-2015, 02:56 PM
  4. Replies: 2
    Last Post: 11-26-2014, 08:24 PM
  5. Conditional Formatting Rows, and Matching and Sorting Two Sets of Data
    By Alan L 185 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-07-2013, 08:16 PM
  6. [SOLVED] Conditional formatting using Icon sets (comparing data to data in array)
    By darth.dims in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-27-2012, 05:36 AM
  7. comparing data and conditional format
    By buhnen in forum Excel General
    Replies: 3
    Last Post: 03-15-2010, 03:43 AM

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