THE RKGBLOG

Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook

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…
remove duplicate contacts in outlook
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:

  1. Back up your .OST or .PST file, in case you make a mistake.
  2. Make a temporary subfolder under your contacts. I named it “Stash”.
    In Outlook2007: Go >> FolderList >> Right Click on ‘Contacts’, select ‘New Folder’.
  3. Drag all your contacts, duplicates and all, into Stash.
  4. If on Exchange, Send/Receive (F9) to sync local with the server.
  5. Export the Stash contacts folder in Excel.
    In Outlook2007: File >> Import and Export >> Export To File >> MS Excel 97-2003 >> Stash.
  6. Open the export file in Excel.
  7. 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.
  8. Decide what constitutes a duplicate record in your world.
  9. 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.
  10. Copy down the C2 formula down to every row in your sheet in Column C.
  11. Sort your sheet, rows 2 to the bottom, by Column C. This sorts your dupes alongside their twins.

    Data >> Sort

  12. Create dupe formula in Cell B2

    =IF(c3=c2, "Dupe", "Unique")
    and then copy it down to all cells in Column B.

  13. Copy all of Column B, then “paste values” into Column A.

    On Excel2007: Home >> Paste >> Paste Special >> Values

  14. 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.
  15. Highlight all the dupe rows at the bottom of the sheet and delete them. Au revoir!
  16. Delete columns A, B, and C.
  17. Save this new sheet with a new name.
  18. 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

  19. Voilà! Deduped contacts. Check that everything went OK. Delete the Stash folder when you’re confident.
  20. 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?)

Technorati Tags: ,

  • Alan Rimm-Kaufman
    Alan Rimm-Kaufman founded the Rimm-Kaufman Group...
  • Comments
    53 Responses to “Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook”
    1. zlatan24 says:

      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.

    2. 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

    3. mgl says:

      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

    4. Great catch — thanks. Corrected.

    5. Adam says:

      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!

    6. RG says:

      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.

    7. Frank Hrobak says:

      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!

    8. 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

    9. Omar says:

      Good one. Thanks.

      Omar

    10. Dave Fetters says:

      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

    11. Alex Krenvalk says:

      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.

    12. Chris says:

      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.

    13. Tom says:

      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?

    14. Eli says:

      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.

    15. James says:

      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…

    16. Terry says:

      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.

    17. Terry says:

      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.

    18. Terry — Thanks for catching the smart quote problem — Fixed above now, hopefully — Cheers — Alan

    19. Pat Flannery says:

      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.

    20. 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

    21. Kenn says:

      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!

    22. James (South Africa) says:

      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.

    23. Alon Luz says:

      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

    24. 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!

    25. “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.

    26. James says:

      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.

    27. Danie says:

      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… :-)

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

      Alon

    29. Mike says:

      THANK YOU! I love you man!! :)

    30. Monal says:

      This is a very good way………………
      But for all novoices using it is a pain…………

      A simpler way is (IF USING EXCEL 2007)

      Export as usual in EXCEL format
      Open File
      Press CTRL + A
      Select DATA>REMOVE DUPLICATES
      In the dailog box check on SELECT ALL
      Click OK

      WHOOOOOOOOOOOOOOOOOOOOOSH
      DONE

    31. Monal says:

      and import it again in OUTLOOK

    32. Eric Duncan says:

      Excellent. Worked like a charm for my exchange-switch-to-new-exchange-account that created too many duplicates. :)

      Thank you!
      -Eric

    33. Hi there
      found your original post and it worked pretty well thanks.
      I have a few suggestions for some improvements though.
      Many of my contact duplicates originated from the days when you had to create multiple entries to store eg a mobile and home number – before many peoples time. So i had GlenMobile, GlenHome, GlenWork etc
      Also, i had some duplicates where the surname and company name fields had been mixed up, so using the standard method meant that extra spaces were removed and the formula THOUGHT the two contacts were the same (this was a problem as i had eg a personal number on one contact, and a work number on the ‘duplicate’ contact, but not both on either.
      So i changed it to concatenate the first 10 chars of each field, and added “-” between each one, so that diffent field positions werent considered the same…
      Anyway, if you want more info, or me to post the formulas / blank sheet, let me know.
      BTW – many automatic methods (inc EXCEL detection) MIGHT fail to identify close matches that arent identical.
      thanks

      Glen
      Glen DOT Collinson AT hotmail DOT co DOT uk
      (email address formatted to reduce some of the spam crawlers) :-)

    34. BTW – i had a company blackberry which i just returned. Found a great tool for opening blackberry backups and viewing ALL content (SMS, EMAIL, etc) and allows you to save them “ABC Amber Blackberry Converter” – it worked a charm (except i ended up with 600 duplicated then, hence looking for this post! lol)

      Glen

    35. Schalk says:

      WOW!

      This is probably one of the best posts I have read in a while! Great work!
      I have been searching for a software utility to do this for me and most of them you have to buy before you can do the work… This is a bit of a longer process but it works 100% and all my duplicate contacts are now gone!

      Thanks again for the great help!

    36. Ajju says:

      Thanks… such a nice, creative and easy solution for a common and big prb of outlook

    37. Weijng Lin says:

      My problem is, contact file doesn’t support multiple language; all characters would display as question marks (?). I feel so sad about that~

    38. param says:

      hi,

      great tip

      i discovered u dont have to play with it at all

      u export it as stash.xls to ur desktop

      and immediately import it back

      just in the dialog box choose dont copy duplicate items

      thats it .

      thanx for the initial help though

    39. Haris says:

      Thanks very much. This article help me to remove the duplicate contacts in My Ms Outlook 2007.

    40. Anthony Bondi says:

      May want to be careful when doing this and you have distribution lists setup in your email contacts folder.

    41. shahab says:

      Root post is a perfect solution.
      @Pram
      I don’t see an option of ‘dont import duplicates’ while importing from .xls to outlook 2007 contacts.

    42. Scrubly says:

      We have a simple cloud based solution to help remove duplicate contacts in Outlook.

    43. Ozzie says:

      @Scrubly
      I’d like to give your solution a shot but it seems that the product is in beta and is only allowing invited users. How would I get an invite?

      Thanks!

    44. Ahmed says:

      Wao, this is wonderful DIY way to remove duplicates. I love it. Thanks:)

    45. RT says:

      Easier way from Microsoft site:

      I copied it and paste here from the link:

      http://support.microsoft.com/kb/299349

      How to Delete Duplicate Items
      Outlook 2002, Outlook 2003, and Outlook 2007
      In Outlook 2007 and in Outlook 2002, point to Current View on the View menu, and then click to change the folder view to a table type view. In Outlook 2003, point to Arrange By on the View menu, point to Current View, and then click to change the folder view to a table type view.

      For example, use the following view and field combinations:
      View Field
      Calendar Active Appointments
      Contacts Phone List
      Inbox Messages
      Journal Entry List
      Notes Notes List
      Right-click a column heading, and then click Field Chooser.
      From the list at the top of the Field Chooser, click to select the All fields.
      Drag the Modified field to the table heading.
      Verify that the duplicate items have a unique date from the original set of items. If it is unique, click the Modified heading so that the items are sorted by this field.
      Click the first item in the set that you want to delete, scroll to the last item in the set that you want to delete, and then click the last item while you hold down the SHIFT key.
      Press DELETE to permanently delete all selected items.
      Outlook 2010
      In Outlook 2010, select the folder for which you want to delete duplicate items.
      Click the View tab in the Ribbon, click Change View in the Ribbon, and then click to change the folder view to a table type view. For example, click the List icon.
      Right-click a column heading, and then click Field Chooser.
      From the list at the top of the Field Chooser, click to select the All fields .
      Drag the Modified field to the table heading.
      Verify that the duplicate items have a unique date from the original set of items. If it is unique, click the Modified heading so that the items are sorted by this field.
      Click the first item in the set that you want to delete, scroll to the last item in the set that you want to delete, and then click the last item while you hold down the SHIFT key.
      Press DELETE to permanently delete all selected items.

      Sorry for formating

      RT

    46. Tangallio says:

      Even easier, way is to export to Excel, delete all contacts from Outlook then re-import but selecting “Do not create Duplicate Contacts”.

      Simples.

    47. chutiephat says:

      Hi all !
      I have the same trouble now. I found this topic and
      http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx
      But i do another way, a very simple way.

      Just export your contact to a file, and import that file back cho MS Outlook, at import step, MS give us a option to chose “Do not import duplicate items”, just check that.
      It work for me.

    48. Wajeeh says:

      I really needed to remove duplicate entries. Used your method and it worked like a charm. Thank you very much

    49. Robin says:

      Thanks so much for this – perfect solution for those of us with a bit of Excel ability!

    50. Alex says:

      Nice when people find simple solutions to what seems to be complex problems. Thank you !

    51. hi. just to share with you my primitive way. i had 27,000 contacts in my outlook which i believe they were wronfully duplicated via iphone- google contact..etc. so now i have to clean up the mess, and i have to thank to your insights, i have done it slightly differently .

      using the same analogy, first i merged the 3 column last – first – middle name. then sort the whole stock of contacts, comparing row below to the row above using a simple logic “if row C3 = C1, “delete”, “maintain”. then i am able to filter out all the duplicates and then sort the whole stock again and delete those under “delete” detection. now i am left behind the clean version, then imported back into the outlook. works fine.

      offcourse, this is going to compromise in terms of the last – first – middle name filing , however in my last 10 years of PDA / smartphone experience, i always practice keying in a person’s full name under “last name” is the easiest.

      all in all, thanks to your hint, i am just doing it a lazier way.

    Trackbacks
    Check out what others are saying...
    1. [...] Another issue to overcome was deleteing over 400 duplicate entries… i found this helpful post: Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook I hope this easy fix helps some of you and saves you hours of trials and tribulations unlike [...]

    2. [...] I ran into the same problem. However, I had a lot of duplicates on my phone (due to poor Outlook integrations w/ other programs). I used the following procedure to remove the dupes and I was able to get my contacts down low enough tosync w/ the car. Here’s the link in case anyone else has the same issue: Easy Free Way To Remove Duplicate Contacts From Microsoft Outlook [...]