I have 2 Excel sheets that I'd like to merge. They are an inventory file and are roughly 5,500 rows each, with each row being a product and each column being an attribute. The first column is the unique identifier.

One copy was taken a few weeks ago to make some updates on various attributes of items. During that time several new products were added so the old file is out of date.

I'd like to merge them by using the new sheet as the primary, and updating this sheet by finding any duplicates between the new sheet and the old sheet, then replacing the duplicate with the value from the old sheet.

For one column this would be possible with a VLOOKUP but I'm not sure how to tackle it for an entire sheet. Any suggestions?

  • You need to use a function or can you use a macro? – Raystafarian Feb 19 '14 at 16:07
  • Macro works, although I'm on Office '11 (Mac) so i don't know if there would be some limitations? – javy Feb 19 '14 at 17:56

If I understand correctly, copy has the current information about products that existed when copy was created (although not necessarily all products were updated), and primary has stale information about those products, plus some new products.

If this is so, then it seems copy could serve as the new basis for primary. In other words, you want to start with copy and just add the new rows from primary, right?


  1. apply VLOOKUP on primary, searching for matching products on copy
  2. filter this column to show #N/A (or sort on this column so the #N/As bunch up to one end)
  3. copy the #N/A rows to copy
  4. blank the VLOOKUP column on copy
  5. be sure to remember copy now has the current information

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.