+ Reply to Thread
Results 1 to 6 of 6

Comparing 2 Excel spreadsheets to find missing records in one

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    West Midlands
    MS-Off Ver
    Excel 2003
    Posts
    8

    Comparing 2 Excel spreadsheets to find missing records in one

    Hi

    I have 2 spreadsheets with the same sort of items on. However book a has about 152 extra lines. is there a formula i can use to find out what is missing off book b? I have cut the files down as the file size was too large. There are approx 23000 lines of items. I need to match the NSN (Nato Numbers) and then the Description.

    Thanks

    Book A.xlsxBook B.xlsx

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,141

    Re: Comparing 2 Excel spreadsheets to find missing records in one

    In Book A in a column on Row 2, type

    =VLOOKUP(B2,'[Book B.xlsx]Sheet1'!$B$2:$B$624,1,FALSE)

    And copy down. Any value returned of #NA will indicate a missing NSN

    Alan

  3. #3
    Registered User
    Join Date
    01-27-2014
    Location
    West Midlands
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Comparing 2 Excel spreadsheets to find missing records in one

    Hi Alan

    Thanks for that its worked perfectly it has found 175 lines. Another question i have is:

    I have inputted prices into Book b column G is there any way i can put the same price into book A column J related to the stock number or will it all have to be done manually?

    Kind regards.

    Richard

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,141

    Re: Comparing 2 Excel spreadsheets to find missing records in one

    Put this in row 2 of Book A column J and copy it down.

    =VLOOKUP(B2,'[Book B.xlsx]Sheet1'!$B$2:$G$624,6,FALSE)

  5. #5
    Registered User
    Join Date
    01-27-2014
    Location
    West Midlands
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Comparing 2 Excel spreadsheets to find missing records in one

    Again a big thanks Alan

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,141

    Re: Comparing 2 Excel spreadsheets to find missing records in one

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

+ 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. Find Missing Entries when comparing two columns
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-31-2013, 12:35 AM
  2. Excel Macro to find missing dates and insert missing date as blank row
    By JevaMarie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:08 AM
  3. [SOLVED] Comparing two columns of words to find out missing
    By Karnik in forum Excel General
    Replies: 4
    Last Post: 10-29-2012, 10:12 PM
  4. Comparing two sheets to find common records
    By kthakur in forum Excel General
    Replies: 6
    Last Post: 07-30-2010, 11:39 AM
  5. Compare 2 excel files, report MISSING and DUP records
    By excelnovic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2009, 11:58 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