+ Reply to Thread
Results 1 to 14 of 14

Comparing 2 non-sequence Sheets in excel to find unique results

  1. #1
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24

    Comparing 2 non-sequence Sheets in excel to find unique results

    Comparing 2 non-sequence Sheets in excel to find unique results

    Hello Everyone,

    I hope you all are doing well and staying safe. I have 2 different datasets Sheet 1 and Sheet 2. In both sheets majority data is duplicate but some data in Sheet 1 is unique which I am trying to capture. Is there a way I can do it easily by using any formula to highlight or extract only unique records.

    NOTE - In both sheets data is not sequenced which means if value (E78.2) is in H2 row, the same value is in (I2) in sheet 2 which makes difficult to search exact unique/duplicate values in excel for me. I have attached a test file showing Sheet 1, Sheet 2 and a manual unique result sheet as well to better understand what I am looking for.

    Your help is much appreciated. Thank you so much for your time and support.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,541

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    If I understand then you could apply the following formula as a conditional formatting rule to cells B2:AA2 on Sheet1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24
    Quote Originally Posted by JeteMc View Post
    If I understand then you could apply the following formula as a conditional formatting rule to cells B2:AA2 on Sheet1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Can you please give steps on how to apply the formula using conditional formatting?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,541

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    Select cells B2:AA2 on Sheet1
    Select Conditional Formatting
    Select New Rule > Use a formula to determine which cells to format
    Paste the formula into the Format values where this formula is true window
    Select your formatting
    Select OK and OK
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    Thank you so much JeteMc. I am trying to follow your steps on a new workbook but its not showing correct results. Can you please help me figuring out why?

    I truly appreciate your help.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,845

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    Is this correct ..
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    Hi John,

    this is 90% correct but still not 100%. if you look at Row # 3 Acct # 30. Your highlighted results are showing as E78.2, F32.9, B18.2 & G40.909 whereas it supposed to show only B18.2 because all other 3 values are present in both sheets 1 & 2 for Account # 30 Row.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,845

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    This JeteMc's version: not wishing to claim kudos for it nor spread the blame. I'll look again later if JeteMc does'nt beat me to it.

  9. #9
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    I really appreciate it if you can. I have been struggling with this for over an year now. Kutools function doesn't work either to show correct results.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,845

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    Change CF formula

    =ISNA(MATCH("*"&B2,Sheet2!$B2:$BO2,0))

    wrong range ... could have been my typo!

  11. #11
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    You are awesome. Thank you so much. I believe it worked. I just need some more time to verify it on the mass data but first 10 rows looks good now.
    Last edited by mimranflmsa; 07-01-2021 at 02:57 PM.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,845

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    All credit is due to JeteMc so if all proves to be OK then please mark as SOLVED ("Thread Tools" at top of first post) and credit JeteMc.

  13. #13
    Registered User
    Join Date
    05-10-2019
    Location
    USA
    MS-Off Ver
    16.0.4639.1000
    Posts
    24

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    Absolutely right. Thank you so much JeteMc.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,541

    Re: Comparing 2 non-sequence Sheets in excel to find unique results

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
    @JohnTopley, Thank You for your help.

+ 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. Comparing Two Sheets with Unique Relationship
    By bobredford in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-02-2017, 06:43 PM
  2. Find missing data by comparing two excel sheets
    By jhonnyexcel in forum Excel General
    Replies: 1
    Last Post: 01-03-2016, 07:35 AM
  3. Find missing data by comparing two excel sheets
    By jhonnyexcel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2016, 06:35 AM
  4. Find missing data by comparing two excel sheets
    By jhonnyexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2016, 06:34 AM
  5. Trying to display results after comparing 2 cells on different sheets
    By Lylebrous in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-03-2014, 06:22 PM
  6. Replies: 1
    Last Post: 09-27-2011, 12:10 PM
  7. Excel 2007 : Comparing cells to find best results
    By berk21 in forum Excel General
    Replies: 7
    Last Post: 11-04-2009, 06:46 PM

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