+ Reply to Thread
Results 1 to 11 of 11

Compare data in two Spredsheets/Sheets - find mismatching data and highlight

  1. 03-10-2017, 10:05 AM #1
    rossyl
    rossyl is offline
    Registered User
    Join Date
    03-07-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Hi Everyone,

    I am hoping for a helping hand relating to my last task.

    I have two spreadsheets.
    Each spreadsheet has a long list of names. The information is all in one column, and each cell in that column has "First Name Last Name".

    I would like to compare one list to the other and find out where one Spreadsheet contains names that are not in the other other Spreadsheet. I would like these non-matching names highlighted.
    Each Spreadsheet starts with a different name and ends with a different name.
    One Spreadsheet has 1500 names. The other has 1200 names.
    The two spreadsheets are not that similar.

    Is there an easy way to compare them to highlight the non-matching names?

    I can copy the spreadhseets in to the same document as separate sheets, if that helps.

    Thank you
    R
    Reply With QuoteRegister To Reply
  2. 03-10-2017, 10:08 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 03-10-2017, 11:13 AM #3
    rossyl
    rossyl is offline
    Registered User
    Join Date
    03-07-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Hi Glenn,

    As requested.

    1. Please find attached a representation of what I have.

    2. I have mocked up the results. Sheet 1 has highlighted those names that are not contained in Sheet 2. That is the information that I want to find. It does not matter if Sheet 2 contains names that are not in Sheet 1.

    3 and 4 - I believe i have followed.

    Thanks again
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-10-2017, 11:42 AM #4
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    you can use the conditional formatting.(under HOME)
    select the range to be colored(sheet1)
    select NEW RULE then choose -> Use a formula....
    then type in the box
    =COUNTIF(Sheet2!$A$1:$A$10,$A$1)=0
    set the colors etc.
    click ok,
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  5. 03-10-2017, 11:47 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Use this as the Conditional Formatting formula:

    =ISERROR(MATCH(A1,Sheet2!$A$1:$A$10,0))

    as shown in the sheet.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-10-2017, 11:53 AM #6
    rossyl
    rossyl is offline
    Registered User
    Join Date
    03-07-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Hi Vladimir,

    I am sure it is probably something I am doing wrong, but i cannot get that to work.

    I cannot see "New Rule" in the Home ribbon on 365. So i searched for it.
    I then get a pop up screen titled "New Formatting Rules"
    The only option that i can see that is similar to above is "Use a formula to determine which cells to format"
    I put your formula into the box.
    I then click format and select red text

    All that happens is that all of the names in Sheet 1 are now in red text
    Reply With QuoteRegister To Reply
  7. 03-10-2017, 11:58 AM #7
    rossyl
    rossyl is offline
    Registered User
    Join Date
    03-07-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Hi Glen,

    I am getting the same result. Everything in Sheet 1 is now highlighted Blue.

    Cannot work out what i am doing wrong.
    Reply With QuoteRegister To Reply
  8. 03-10-2017, 12:01 PM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Check that you have entered the CF formula correctly. Occasionally Excel takes a dose of the head staggers and resets the range!! Do you accept that it was working OK in the sheet that I posted?
    Reply With QuoteRegister To Reply
  9. 03-10-2017, 12:01 PM #9
    rossyl
    rossyl is offline
    Registered User
    Join Date
    03-07-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Worked great in the sheet that you posted.
    Reply With QuoteRegister To Reply
  10. 03-10-2017, 12:06 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    Then check the CF formula in your real sheet. Particularly the A1 in ISERROR(MATCH(A1,Sheet2!$A$1:$A$10,0))
    Reply With QuoteRegister To Reply
  11. 03-10-2017, 12:08 PM #11
    rossyl
    rossyl is offline
    Registered User
    Join Date
    03-07-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Compare data in two Spredsheets/Sheets - find mismatching data and highlight

    I think....it might be working
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare Data & highlight
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-04-2015, 11:31 AM
  2. How to compare data in rows and highlight if same?
    By Murmeln in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2015, 02:52 PM
  3. Linking data in spredsheets using tables (or not)
    By nikospur in forum Excel General
    Replies: 1
    Last Post: 11-26-2014, 06:27 PM
  4. Compare, Find and Highlight Duplicates in multiple sheets
    By EvilleT in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-19-2013, 08:42 PM
  5. [SOLVED] Compare Sheets Find Missing Data
    By ClarkKent88 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-08-2012, 01:51 AM
  6. Compare data in multiple columns to find duplicates & conditionally highlight
    By ndube50 in forum Excel General
    Replies: 5
    Last Post: 07-05-2011, 06:25 PM
  7. Compare two Sheets - Add New Data, Update Differences, Highlight Void Data
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-18-2010, 04:03 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1