+ Reply to Thread
Results 1 to 15 of 15

Using Exact function to color cells that do not match.

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Using Exact function to color cells that do not match.

    Good morning,

    I have a spread sheet which easily could go to 300 lines of information. It used data from two different systems and compares them.

    Using Conditional Formatting I can determine which piece from each system does not match (in the sample it is column B and column I). This only works to a degree. As you can see in the sample B4 and B5 match so no formatting takes place.

    The comparison should really lie between B and I and not within each column. The only way I can think of doing this is to run the Exact function. And this works...obviously.

    However, I want it to be readily visible to the user. I would like to format the cells in B and I that do not match making them stand out rather than searching through the text created by the function.

    I am thinking it may be a nested formula?

    Anyways...I would appreciate any help I can get.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,708

    Re: Using Exact function to color cells that do not match.

    Select B4 down & I4 down & use this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-23-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Using Exact function to color cells that do not match.

    Okay...but how does this color the cells that do not match one another (e.g. B6 and I6)? The coloring of the cell is my issue.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,708

    Re: Using Exact function to color cells that do not match.

    You use that in conditional formatting. Although none of your numbers in col B match col I

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,199

    Re: Using Exact function to color cells that do not match.

    If I understand correctly, you need two rules.

    For B4:

    =AND($B4<>"",COUNTIF($I:$I,$B4)=0)

    For I4:

    =AND($I4<>"",COUNTIF($B:$B,$I4)=0)
    Attached Files Attached Files
    Last edited by AliGW; 04-25-2022 at 09:48 AM. Reason: Typo fixed.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,708

    Re: Using Exact function to color cells that do not match.

    Ali those formula are the same.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,199

    Re: Using Exact function to color cells that do not match.

    Thanks - I've fixed them.

  8. #8
    Registered User
    Join Date
    04-23-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Using Exact function to color cells that do not match.

    Hello Fluff13,

    The formula did not work. If you could send the spreadsheet back to me showing it working then I might get a better understanding of inputting the formula. Just make B4 the same as I4 it you want a match to show.

    Thanks

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,199

    Re: Using Exact function to color cells that do not match.

    Did you look at my suggestion? Is that what you want?

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,708

    Re: Using Exact function to color cells that do not match.

    Here you go.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-23-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Using Exact function to color cells that do not match.

    Good morning AliGW,

    Your suggestion worked brilliantly! Thank you so much.

    Have a great day/evening!

    FranAgrippina

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,199

    Re: Using Exact function to color cells that do not match.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  13. #13
    Registered User
    Join Date
    04-23-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Using Exact function to color cells that do not match.

    Thank you Fluff13.

    That is what I typed in but could not get it to work.

    Interesting.

    I will consider your response as well.

  14. #14
    Registered User
    Join Date
    04-23-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Using Exact function to color cells that do not match.

    Thank you both.

    I believe I can handle it from here.

    Have a great day/evening. You both have been very helpful!


    FranAgrippina

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,708

    Re: Using Exact function to color cells that do not match.

    Glad to help & thanks for the feedback

+ 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] Regex exact match function
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 05-03-2019, 02:40 AM
  2. Validating 2 cells goes wrong, finds first match instead of exact match.
    By EricNL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2015, 08:38 AM
  3. [SOLVED] Found function with exact match
    By bimo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-06-2014, 04:27 AM
  4. FIND function exact match
    By kgolding in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 12-13-2013, 10:41 AM
  5. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  6. [SOLVED] Exact Value match in Lookup function
    By cdjindia in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-28-2012, 08:48 AM
  7. Font color of exact function return in excel should be customize
    By Thakku Ramanan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2005, 02:55 AM

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