I have a pretty simple question and google can't seem to return what I'm looking for.

Essentially I have two sheets in one workbook, both contain the exact same columns. The primary key (and first column) of these two sheets are email addresses. I would like to merge the two sheets and remove duplicates based on the primary key (email addresses).

Note that I am using Microsoft Excel For Mac 2011

Thank you in advance

  • If you right click on the tab on the bottom of Excel (where it has the sheet name), there should be an option that says Move or Copy... You could try using that, as I believe it can merge sheets together. This may not be what you are looking for though, hence the comment form. – Thomas Reinstate Monica Myron Jul 23 '15 at 17:17
  • no sample data shared.. though to help/assist.. | : – p._phidot_ Oct 15 '18 at 16:21

What you need to do is copy all content from one sheet and paste it directly below the info in the other sheet so the columns line up. Then select all columns, go to Data > Remove Duplicates.

  • I don't think this will work. My sheet(s) have like 15 columns. Email Address is the primary column/I want to use it as the primary key. So when Excel finds two of the same emails, it deletes the entire row for one of them. Each duplicate will necessarily have at least 1 column cell value that differs meaning that if it removes duplicates based on exactly the same rows, it will never find any duplicates. – plambre Jul 23 '15 at 17:34
  • I misunderstood, sorry. Do you want to merge information in other columns that may be different from sheet to sheet? If so, you can use a combination of concatenation with a VLOOKUP on one of the sheets. I can give an example if that's what you mean. – finc Jul 23 '15 at 17:39
  • Basically like this: Merge two sheets and dedup based on email address. When it finds the same email address, delete the entire row of one of them (likely the lookup table row). No updating is necessary. – plambre Jul 23 '15 at 17:41
  • Then Remove Duplicates is your answer, you just need to tell Excel which columns to use for comparison. This answer explains it better than I can. – finc Jul 23 '15 at 17:44
  • Alright, I think that finally worked. The issue (i think) was that Excel For Mac doesn't have the checkbox for "my data has headers". I removed the headers and it seemed to work. – plambre Jul 23 '15 at 17:54

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.