How can I easily merge two Excel spreadsheets, then delete items that were not found in both?

forrest moncrief May 10, 2013
Pinterest Stumbleupon Whatsapp

I have two Excel spreadsheets. One has 20 named items with a device code in two columns. The second has hundreds of items entered the same way with other info like prices and etc.

I want to move all the items from spreadsheet 2 to spreadsheet 1 and have all the items match up (for prices and so on) and have the items not included in spreadsheet one disappeared or be grouped for easy deletion.

Any way to do this?

  1. Scott Kupferman
    June 7, 2013 at 12:37 am

    You could pull both into Access, run a query to find unmatched items but as a maketable query. You would then have a result set in the form of a table that you could then export back to Excel.

  2. Alan Wade
    May 12, 2013 at 6:24 am

    There are some good VLOOKUP examples here that should give you an idea on how to adapt to what you want to achieve:
    http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

  3. Oron Joffe
    May 10, 2013 at 8:57 pm

    One way would be to add a column (let's call it column Z) to the second spreadsheet, and put into it a lookup function (e.g. VLOOKUP() ) which would look for the code in the first spreadsheet. If it finds it, it should display the code, and if not, it should display nothing (or a special code, such as "X" or "not found").
    Once that is set up, you can sort on column Z and the unmatched codes will be grouped together, ready for deleting.

    • Rajaa Chowdhury
      May 11, 2013 at 12:34 am

      Pretty smart. Yes VLookup function should do the trick.

    • Bud Ingersoll
      May 12, 2013 at 4:06 am

      Damn! I just developed a lengthy response explaining the use of nested IFs and ANDs, before I read the full explanation of the problem. But more embarrassing was Joffe's solution.. Much cleaner than mine. Good job.