MailChimp automatically de-dupes your email lists whenever you import them. We’ll even spit out the rejected emails, in case you want to see who the dupes were.
But some people like to do things manually, because they enjoy pain.
So here’s an Excel formula that you can use to compare and remove duplicates from two lists…
Instructions for removing duplicates from your list with Excel:
- Open up Microsoft Excel.
- Paste your entire list into Column A.
- Sort the entire column alphabetically (A > Z). This is very important.
- Click on and highlight Column B.
 |
- With Column B highlighted (press the gray B button above the column to highlight the entire column), enter the following into the “formula” box in Excel.
=IF(TRIM(A1)=TRIM(A2),”",TRIM(A1))
- After the formula is filled in, and with the entire Column B still selected, go to the menu in Excel, and choose: Edit > Fill > Down.
- This will fill Column B with a new, updated list, with all duplicates removed.
- Now, you’ll want to copy that column into a new spreadsheet. “Copy & Paste” won’t work…
- Select Column B, and choose Edit > Copy.
- Create a new spreadsheet, and select Edit > Paste Special > “Values”.
- Now you should have the final, updated list. You can sort it now, and remove blank lines.
Is the page BLANK? After you paste into a new sheet, blank entries from your list show up at the top, so if you have a very large list, you might have a lot of blank lines near the top. If so, scroll down to see your list.
cat ~/list | sort | uniq
The two excellent text editors for Mac BBEdit and the free Text Wrangler both has “Remove duplicate lines” as a menu item. Just paste your email addresses, one on each line, into a document, “Remove duplicate lines” and you’re good to go!
Willy T. Koch
Norway
Text Wrangler did the trick!!
I wasn’t able to keep all my data together but I am a happy camper!!!
You can also save the trouble of worrying about sorting (or whether the sort worked) by using a count instead:
=if(countif(A:A,A2)>1,”",A1)
Of course, you lose the trim since you can’t trim a whole column at once, but you probably want to do that first separately anyway to avoid errors. Created a pre-trimmed column first…
Never mind, this isn’t actually shorter after all (although it may be more reliable).
Whoops, meant A2 at end of that formula…
This post was just a trap to see how many Excel nerds I could reveal. Heh, thanks for all the tips, guys!
This Excel formula comes from a help article buried deeeeeeep within the bowels of our knowledge base.
We used to point to it a lot, back before MailChimp had de-duping built in.
I use a piece of software called DeDupe4Excel http://www.dqglobal.com/deduplication_software_excel.html
I don’t work for them and I’m not on commission. But it is really good as it dedupes against all your data; name, address, zipcode, email etc and you can set the percentage of certainty as well (e.g. does mike = michael)
Put the data you want into Column A. Sort alphabetically. Make sure you have no header. In cell B1 paste =IF(COUNTIF($A$1:A1,A1)>1,”duplicate”,”") and then fill this down to the bottom.
Turn on autofilter and filter for the word “duplicate” in column B and then delete all those lines.
An alternative method is to simply use a pivot table.
1. Create the pivot table.
2. Highlight the column(s) against which you want to dedupe.
3. Drag the column you want to dedupe onto the pivot table.
Now you see your results.
4. Copy the list.
5. Paste Values…
and you’re done.
FOUR CLICKS TO DE-DUP:
Some guys have some really nerdy and “clever” formuli.
Rather than use a sledgehammer to crack a nut, try copying your list (to be safe), then using your COPY – do what it says here:
http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx
Doesn’t work. I get an error that says not to use = . Help?
If you import your list into MailChimp, we automatically de-dupe for you.
This is seriously an idiot exercise… NOW, what would be good is a system to dedupe a 7 field Excel file (first, last, address, etc.) against a single filed email list with the result being a deduped 7 field Excel list.