Results 1 to 10 of 10

Thread: Comparing 2 worksheets and displaying differences

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparing 2 worksheets and displaying differences

    Hello,

    I need to find a formula that will compare data across 1 column in 1 worksheet with 1 column in another worksheet and report back on whether the data appears in both columns or not. Example scenario: A customer sends across a list of product codes they would like us to quote, i have this list in sheet 1 on excel. The quote is then put together in sheet 2 however the products may not appear in the same order. I want to be able to check that we have covered off all the products customer requested and also identify any which do not match.

    I found this formula which should do the job:

    =IF(Sheet1!A1<>Sheet2!A1,"Sheet1:"&Sheet1!A1" vs Sheet2:"&Sheet2!A1,"OK")

    This would check the same cell in each sheet and tell me what info is displayed in each cell however i want it to search within the whole column, so in theory the blow should work as it's a range.

    =IF(Sheet1!A:A<>Sheet2!A:A,"Sheet1:"&Sheet1!A:A" vs Sheet2:"&Sheet2!A:A,"OK")

    It still seems to only check the same cell though. Any ideas on how i get it to check the entire column?

    Many Thanks

    Sharon

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,067
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    Hi & welcome to MrExcel.
    How about in Sheet2
    =IF(ISNUMBER(MATCH(A2,Sheet1!A2:A1000,0)),"Ok","")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    It looks as though this may be a way around it, would just need to run on both sheets to find all the changes.

    Many Thanks

    Sharon

  4. #4
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    900
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    Perhaps this little tool might help:
    https://jkp-ads.com/download.asp#Compare2Tables
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,067
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    You're welcome & thanks for the feedback.
    To use the formula on the other sheet as well, just change the sheet reference in the formula.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    As i want to see the results of both on 1 sheet i created a 3rd sheet which is looking up the data in sheet 1 and checking it appears in sheet 2 and vice versa. However Sheet 2 has less data than sheet 1, at the moment the formula will keep populating with blank space to indicate a problem, is there a way i can get it to identify it has reached the end of the data on that sheet? otherwise i'm going to be looking for problems that aren't there.

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    Quote Originally Posted by jkpieterse View Post
    Perhaps this little tool might help:
    https://jkp-ads.com/download.asp#Compare2Tables
    Sadly i cannot apply the add-on as i'm needing this on a work computer.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,067
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    Not sure I understand what you're saying, but would this work.
    =IF(Sheet2!A2="","End",IF(ISNUMBER(MATCH(Sheet2!A2,Sheet1!A2:A1000,0)),"Ok",""))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    Yes that's perfect thank you.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,067
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Comparing 2 worksheets and displaying differences

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •