Ghetto Style De-Duping
Ok, I’ve been getting flack for not having enough marketing stuff… so here’s some Excel Judo for you to work on.
You have a list of 8,000 contacts in excel, you need to de-dupe them (remove the duplicates). Here’s a quick way to clean up the majority of them:
- Sort by email address
- In the first open column create an “IF” statement (click the f(x) button and select “IF”). If the email address on this row equals the email address one cell above it then this cell is “1″ if not “0″
- Copy that formula to all the rows
- Select that column and copy it
- From the command bar choose “Paste Special” and select “Values” - this strips out the formula and leaves the 1’s and 0’s
- Sort the table by this column with 1’s and 0’s and then delete all the rows of 1’s in one shot
Viola! You’re done. Now you could do this with a SQL command, but that’s a lesson for another day.
Posted: January 25th, 2008 under Email Marketing.
Trackback Address
Comments: 5
Comments
Comment from Mark Harrison
Time: January 25, 2008, 4:19 pm
The version I use:
A1 - the first data item
A2 - the second data item
An - the nth data item
B1 - IF(A1=A2,”",A1)
B2 - -copy down from B1 -
B3 - -copy down ditto -
You can then copy/paste the final column in one go 
Comment from Vero Pepperrell
Time: January 25, 2008, 5:03 pm
Alternatively, use a great email marketing service like Campaign Monitor and let them handle your de-dupes before anything goes out. Then export your list back out, and tadah, clean data including hard/soft bounces, unsubscribes and incorrectly formatted addresses sifted out.
Comment from Danielle Buczek
Time: January 25, 2008, 7:14 pm
Wow, so many ways to do this! I use the EXACT function. If the two items to compare are A1 and A2, the formula is EXACT(A1,A2) and returns a value of TRUE or FALSE. Then it works the same as what John said–copy down, paste special, values, and cut out all the TRUEs.
Comment from john
Time: January 25, 2008, 8:17 pm
Vero, good point - I use Ringlead for heavy lifting, and have also used ConstantContact and ExactTarget.
Danielle - Nice, didn’t know about the EXACT function…
Comment from Doug Haslam
Time: January 25, 2008, 12:25 pm
Viola?
Actually, nice tip, thanks. I wish I thought of this in my list de-duping days.