Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook

Home
Rkgblog
Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook

Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook

Title:	Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook
URL:	http://www.rimmkaufman.com/rkgblog/2007/07/30/remove-duplicate-outlook-contacts/
Printed:	July 4, 2009
Source:	The Rimm-Kaufman Group Blog, info@rimmkaufman.com

Off-topic warning: this post isn’t about online marketing. Of course, marketing is about staying in touch, and a tidy Contacts folder helps stay in touch, but that’s a stretch…

I am not sure why Microsoft Outlook creates duplicate contacts, but too it often does. Palm Pilot sync hiccups, Exchange hiccups — whatever the reason, suddenly you notice that a large fraction of your valuable contacts are in there two times or more. Ugh! It is annoying Outlook doesn’t prevent or fix ‘pure’ dupes automatically, but it doesn’t.

There are many software programs to help, but they’re crippled in their freeware versions, and seem to require lots of button pushing and prompt confirmations.

Here’s a free, relatively fast, hands-on deduping approach that worked for me: export contacts to Excel, create a signature field indicating uniqueness, sort and dedupe on the signature field, then re-import the cleaned contacts into Outlook. Here’s a detailed step-by-step recipe:

Back up your .OST or .PST file, in case you make a mistake.
Make a temporary subfolder under your contacts. I named it “Stash”.
In Outlook2007: Go >> FolderList >> Right Click on ‘Contacts’, select ‘New Folder’.
Drag all your contacts, duplicates and all, into Stash.
If on Exchange, Send/Receive (F9) to sync local with the server.
Export the Stash contacts folder in Excel.
In Outlook2007: File >> Import and Export >> Export To File >> MS Excel 97-2003 >> Stash.
Open the export file in Excel.
Insert three new blank columns of the far left, so the first column of data now lives in column D.
In Excel2007: Home >> Insert Cells >> Insert Column.
Decide what constitutes a duplicate record in your world.
In Cell C2, insert a formula concatenating together the fields which define dupeness.
1. For lastname-phone dupes, enter (on my version of Outlook):
  =CONCATENATE(G,AI,AM)
2. For full dupes, enter
  =CONCATENATE(D1,E1,F1,G1,H1,I1,J1, K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,w1,x1,y1, z1, AI1, AM1, BI1)
  The columns actually go over to CP, but I got lazy typing all that in. An Excel function to concatenate ranges would have helped.
Copy down the C2 formula down to every row in your sheet in Column C.
Sort your sheet, rows 2 to the bottom, by Column C. This sorts your dupes alongside their twins.

Data >> Sort
Create dupe formula in Cell B2

=IF(c3=c2, "Dupe", "Unique")
and then copy it down to all cells in Column B.
Copy all of Column B, then “paste values” into Column A.

On Excel2007: Home >> Paste >> Paste Special >> Values
Sort your sheet again, again from Row 2 downward, this time on Column A descending. This sends the dupes to the bottom and the uniques to the top.
Highlight all the dupe rows at the bottom of the sheet and delete them. Au revoir!
Delete columns A, B, and C.
Save this new sheet with a new name.
Import the sheet back into Outlook, typically into your root Contact folder.

In Outlook 2007: File >> Import and Export >> Import From Another Program Or File >> MS-Excel 97-2003
Voilà! Deduped contacts. Check that everything went OK. Delete the Stash folder when you’re confident.
If something goes wrong, Microsoft’s scanpst.exe or scanost.exe may be able to help repair your OST or PST file. (But you made a backup in step 1, yes?)

If you like this post, consider subscribing to our RSS feed. You can also have new posts sent to you via email.

Repairing A Broken or Corrupted Outlook PST My Outlook PST file died badly this week. The key steps for me to recover the file was scanpst.exe and using Outlook's "Export As PST"...
What We’re Reading: Using Microsoft Outlook to Get Organized and Stay Organized, by Sally McGhee I've been enjoying Sally McGhee's book on Outlook tips for managing email, tasks, and calendar intelligently....
Remove Permanent Marker From Whiteboard To erase permanent marker from a white board, first draw over the mistake with a dry erase marker, then erase....
Free alternatives to Webex, Camtasia, etc Interesting free and/or open source alternatives for handling online meetings, videos, screenshots, and files....

Comments

zlatan24, August 1, 2007:

I don’ t cross with problem, but i know program able work with Outlook - fix pst, it can recover data file may be damaged by an unexpected power failure, a hardware or network crash and a number of other external factors, program can also be used to recover data from Microsoft Exchange .ost files and convert them into Microsoft Outlook .pst files, also will display a progress report showing recovery statistics, such as the number of recovered folders, items, paths to the source and resulting files.
Mike McSharry, August 17, 2007:

Thankd for that!! worked a treat - easier to do than it looks
Quick points to help a bit ..
On step 5 make sure you’ve got default map showing to get the full record.
Many thanks
Mike
mgl, September 14, 2007:

In step 12 it should read:

Create dupe formula in Cell B2
=IF(c3=c2, “Dupe”, “Unique”)
and then copy it down to all cells in Column B
Alan Rimm-Kaufman, September 14, 2007:

Great catch — thanks. Corrected.
Adam, October 17, 2007:

This is the best thing ever, I spent so long trying to get rid of my dupes (and didn’t want to spend any money) and this is exactly what I was looking for. Thank YOU!
RG, December 20, 2007:

This was very helpful. I was able to do other sorts and deduplication using this approach. Excel does present itself as a great way to clean up your contact database.
Frank Hrobak, January 4, 2008:

Alan,

Thanks for this procedure to delete dupes. Does it take into account categories under which your contacts are filed such as “Business”, “Personal”, etc. when you import the cleaned up excel file back into Outlook (I am using 2007).

Thanks!
Alan Rimm-Kaufman, January 4, 2008:

Hi Frank –

I recall this preserves categories, yes.

If you want to dedupe w/in categories — that is, you want “John Smith” in Family to be different than “John Smith” in Business — then add the category column to the uniqueness formula in step 9.

That change would allow inter-category dupes, but squish intra-category dupes.

Happy Friday –

Alan
Omar, January 8, 2008:

Good one. Thanks.

Omar
Dave Fetters, January 19, 2008:

Oh, that was sweet! I have a spreadsheet that I’ve been trying to eliminate duplicated cell values (not whole rows) on for the better part of two days. I wanted to do it all automatically, and was finally going to just write some VBA code to do it, but this is so simple - OK it’s not fully, automatic and it takes a couple of steps, but the concept is so simple…that this is the way I plan to teach it to my co-workers. Now all I have to do is to try to move the unique values to the first occurrence spot, but that’s another day. Thanks
Alex Krenvalk, January 25, 2008:

Try-recover outlook contacts, may recover Outlook files and recover Outlook messages from corrupted *.ost files of Microsoft Exchange versions 5.0, 5.5, 2000, 2003, runs under all Windows operating systems starting from Windows 98 (Windows 98, Windows Me, Windows NT, Windows 2000, Windows 2003, Windows XP); The Outlook PST recovery tool compatible with Windows Vista, program recover Outlook files and data from corrupted pst files of all Microsoft Outlook mail clients, from Microsoft Outlook 97 to Microsoft Outlook 2003. To save the recovered information as files with the *.pst extension, you should have the Microsoft Outlook 98 mail client or higher installed on your computer.
Chris, February 9, 2008:

Great tip. I don’t have this problem with Thunderbird but I certainly have customers who have contact duplication problems in Outlook. As an aside I would point out to anyone using Outlook’s inbuild repair tool that it only works reliably if each of your pst files is under 512mb, so remember to split them up as you go along. The tools suggested above from recoverytoolbox may well work with larger .psts, and I’ll definitely be having a quick look.
Tom, February 22, 2008:

When I do row B, it gives an “The formula contains unrecognized text”… And so when I paste the values, it’s just #NAME?….
Frustrating. Any advice?
Eli, April 7, 2008:

Of course, another very easy way is to use address book in apple OS X. 2 clicks of a mouse and all of your duplicates are merged while providing control of how, if you want to.
James, August 4, 2008:

13. Tom, February 22, 2008:

When I do row B, it gives an “The formula contains unrecognized text”… And so when I paste the values, it’s just #NAME?….
Frustrating. Any advice?

Change =IF(c3=c2, “Dupe”, “Unique”)
To ==IF(c3=c2, 0, 1) WITH “0″ being the duplicate record.

I’m not sure why Excel didn’t like the words “dupe” and “unique”, I’m using Excel 2007 and got the same result as you. For the record, new feature in Excel 2007 is “Remove Duplicates” and you can specify which Field(s) to dedupe on…
Terry, August 16, 2008:

13. Tom, February 22, 2008:

“When I do row B, it gives an “The formula contains unrecognized text”… And so when I paste the values, it’s just #NAME?….
Frustrating. Any advice?

Change =IF(c3=c2, “Dupe”, “Unique”)”

You pasted from the web page, right? You got smart quotes. Change it to

=IF(c3=c2, “Dupe”, “Unique”)

Then, the error goes away. Thanks to my son for seeing this when I showed him the same error.

Then, he points out that Excel 2007 “Remove Duplicates” feature. Much easier!!! I’ve finally found something to like about Office 2007.
Terry, August 16, 2008:

OK, your website changes straight double quotes to smart quotes, so my post doesn’t look right. Just replace the quotes in your formula in Excel with the double quote on your keyboard at shift-apostrophe.
Alan Rimm-Kaufman, August 17, 2008:

Terry — Thanks for catching the smart quote problem — Fixed above now, hopefully — Cheers — Alan
Pat Flannery, September 25, 2008:

Alan, I have succeeded in capturing a list of returned “failures” from a fairly large “send list” and have put it into an Excel column. I have exported from Outlook to Excel and inserted the “failures” as a new column. How do I correlate this “failure” column with my “email” column? I want to selectively delete the contents of “email” fields that have an identical email address in the “failure” column. I have tried to write an “if” command but failed. Any ideas? The purpose is to eliminate email addresess that are invalid or rejected.
Alan Rimm-Kaufman, September 25, 2008:

Hi Pat –

If you’re sending email in any volume, you’d want to hire an email shop to handle the sending and the opt-outs and so forth.

That said, if you have a list in Excel as column, and you want to delete entries in that first column (the ALL list) which also occur in a second column (the BAD list), I’d suggest using a VLOOKUP to indicate which of ALL are also in bad. Then, you could PASTE VALUES on those indicator values, sort the ALL list by the value-pasted indicators, and the bad elems will sort to the top or the bottom, then easily removed. You can learn how to use VLOOKUP in the Excel documentation.

But, do rent an email app or hire an email service to make your life easier.

Cheers

Alan
Kenn, October 22, 2008:

Thanks for the WONDERFULLY CREATIVE solution to a common Outlook problem. I too tried a couple of software solutions, but they didn’t allow “complete” duplicate detection. They called a contact a duplicate if it has the same first name, last name and e-mail. Obviously, if there is a contact with an updated phone number, the software may delete EITHER (since it doesn’t take numbers into account).

Keep up the good work!
James (South Africa), November 14, 2008:

I can now delete all those rubbish apps that only let you delete 20 at a time and bug you with all their adware. Thank you so much for a really simple solution.
Alon Luz, December 11, 2008:

Alan

I started doing as you suggest and I found much easier way..
let me know if you see any problem with it.
I created new folder (temp)
moved all contacts to the new folder
export from new folder to excel file
now, I import back from the excel file to “contacts” folder
in the import option, i selected: “Don not import duplicate items”

thats it…all contacts are in contatc and no duplication
now you can delete the temp directory.

can you please send me email if you see any problem with it?
Thanks
Alon Luz
Alan Rimm-Kaufman, December 11, 2008:

If it works, all good! Your approach is less steps, an advantage. The excel approach described above lets you define exactly what constitutes a dupe, which might be important in some contexts. Good luck, and thanks for the comment!
Stephen Martin, January 24, 2009:

“Eli, April 7, 2008:
Of course, another very easy way is to use address book in apple OS X. 2 clicks of a mouse and all of your duplicates are merged while providing control of how, if you want to.”

Alan R-K… Thanks alot for your solution.

Eli… Awesome! I’m syncing Outlook with my iMac/Leopard/Parallels anyway, and your 2-clicks on the Mac = elation! Wow, glad I read the comments. Dupes are done already! No spreadsheet, no cut/paste, just click and click, done. The Mac rules.
James, February 5, 2009:

Hi, I am having a problem trying to import the new excel file. It tells me a I need to define a name range before inporting the new file. I looked in Excel’s help but couldn’t figure it out.
Danie, February 10, 2009:

Thanks for this advice!

Everything went well, but it imported 285 of my 500 contacts!! After trying different things unsuccesfully, I eventually figured out that there is a “Named Range” (Insert>>Name>>Define) in the Excel file called “Contacts” which should point to the complete range of ALL your contacts in the Excel file. E.g. if you have 500 contacts, it should read something like this: =Contacts!$A$1:$CN$500

Changing the range to include all contacts, SOLVED my problem. Thought somebody else might run into this… :-)
Alon Konchitsky, March 6, 2009:

Perfect. Thank you. I removed 3000 duplicates. Every time I sync by BlackBerry, duplicates are generated.
Thanks for this advice!

Alon
Mike, June 22, 2009:

THANK YOU! I love you man!! :)

Your Comment

Trackback

http://www.rimmkaufman.com/rkgblog/2007/07/30/remove-duplicate-outlook-contacts/trackback/

Blogs Citing This Post

Pingback: Duplicate Address Book Entries / Contacts - BlackBerryForums.com : Your Number One BlackBerry Community on January 31, 2008