- July 30, 2007
- 15 comments
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.
- For lastname-phone dupes, enter (on my version of Outlook):
=CONCATENATE(G,AI,AM) - 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.
- For lastname-phone dupes, enter (on my version of Outlook):
- 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.
Similar Posts
- Repairing A Broken or Corrupted Outlook PST
- Keeping Key Files Close At Hand: Encrypting A USB Thumb Drive
- What We’re Reading: Using Microsoft Outlook to Get Organized and Stay Organized, by Sally McGhee
- Excel Training Info from Juice Analytics
- RegHack To Stop Windows From Opening Folders In New Windows
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
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.
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
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
Great catch — thanks. Corrected.
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!
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.
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!
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
Good one. Thanks.
Omar
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
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.
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.
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?
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.