0
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
plambre
Posted 2015-07-23T17:09:21.933
Reputation: 111
1
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.
finc
Posted 2015-07-23T17:09:21.933
Reputation: 11
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 – 2015-07-23T17:34:17.267
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 – 2015-07-23T17:39:14.160
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 – 2015-07-23T17:41:27.130
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 – 2015-07-23T17:44:25.730Alright, 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 – 2015-07-23T17:54:47.263
Glad you got it sorted. – finc – 2015-07-23T17:55:31.633
Argh, maybe not. I'm still seeing duplicate values: ex. A113 = adamantios.corais@gmail.com and A114 = adamantios.corais@gmail.com – plambre – 2015-07-23T18:02:33.163
Is there a space in one of the entries? Excel won't read these as duplicates. Try selecting whole email address column, Ctrl+H (or Apple equivalent) to find and replace, put one space in the find box and nothing in the replace box. Then rerun Remove Dups. – finc – 2015-07-23T18:06:41.257
Tried that and it doesn't seem like it. I think I'm just going to give up and accept the duplicates – plambre – 2015-07-23T19:31:22.997
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 – 2015-07-23T17:17:54.757
no sample data shared.. though to help/assist.. | : – p._phidot_ – 2018-10-15T16:21:35.763