+ Reply to Thread
Results 1 to 3 of 3

Compare data in one workbook across two sheets

  1. #1
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Compare data in one workbook across two sheets

    Every month I make a stock compare of what is in our system and what the cold store tells us is in their system. Because the combination of material and batch number is unique, I use CONCATENATE as a criterium for SUMIFS to calculate what the cold store has in their system and compare that value to what is in our system. So far, so good.

    What I am missing, is the materials that is in our system but not in the cold store, and vices versa. I can use the side by side view to compare data and find the differences but this takes quite some time. Is it possible to use a formula to look those materials up? And perhaps copy them to at least the tab cold store? Or another suggestion to solve this?

    In the attachment a sample. On tab cold store and on tab SAP I have highlighted a material that does not occur in the other sheet.

    Thanks in advance for your help, have a nice weekend and be safe!
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Compare data in one workbook across two sheets

    I fear you've picked a bad examle there since only row 20 has a Material/Batch number that is different when compared with the other sheet.

    However use in G2 copied down of the Cold Store sheet

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and similarly in G2 of the SAP sheet, changing the SAP! reference to 'Cold Store'! and the text message.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Re: Compare data in one workbook across two sheets

    Hi Richard, I apologize for the bad example. I forgot to save the file before attaching. My mistake. However the solution you gave works perfectly. Thanks.
    Last edited by Marion59; 04-06-2020 at 04:59 AM.

+ 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. Replies: 0
    Last Post: 07-27-2016, 07:58 AM
  2. Replies: 1
    Last Post: 07-23-2015, 10:15 PM
  3. Replies: 4
    Last Post: 04-18-2013, 07:25 AM
  4. [SOLVED] compare data from two different sheets in same workbook
    By akulka58 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-26-2012, 03:43 PM
  5. [SOLVED] Compare sheets in workbook
    By JTPhillips in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-14-2012, 10:29 PM
  6. How do I compare cells in multiple sheets in a workbook
    By rs56369 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2008, 06:30 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