How to handle Outlook contacts that give out too many duplicates when merged?

[SuperTom]

Hello,

We just bought a CRM software and until today, each salesman had his own contact list in Outlook. I retrieved, for each of them, XLS files from Outlook and I copy-pasted it in one file. Thing is, now I have hundreds and hundreds of duplicate records . I would like to get rid of all the duplicates, how can I handle this easily?

[NvestWeb]

The CRM should be able to handle this at the time of import. You should be able to define a group of fields that represent a "key", i.e., together they form a unique record (only one can fill the criteria). At the time of import, you should be prompted for how you want to handle a duplicate (based on the key), i.e., delete, update/modify, etc.

If the CRM doesn't handle it, MS-Access can do the same thing for you in a similar fashion.

Peter

p.s. This is a good time to update mailing addresses, too. Either work with your local Post Office to send the list through for an update (a.k.a., sanitize the list), or there are many third-party software and services vendors that can do it for you, or use their product/service. This eliminates the re-do from putting dirty addresses into a clean database when it comes to mailing time.

Sales is more than just a numbers game...

[kruser]

Yes I agree the software should have the capabilities to remove duplicate entries.

My second comment on this is more business related than technical. How do the salespeople feel about everyone having access to their individual mailing list? I would view it as a breech of unspoken ethical boundaries.

[BigD]

From my experience based on the dozens of occasions in which clients have had me amalgamate names-and-addresses lists of various sorts (Outlook, Excel, Access, Word, ACT! and so on), I doubt that CRM software will remove duplicates satisfactorily.

People keep their names and addresses lists in different ways. For example not everyone allocates addresses rigorously to same first name, last name, address line 1, address line 2, address line 3, town/city, county/state, postcode/zip code field pattern.

On top of that there is the problem that most lists are flat file. Usually, this means that in business contacts lists there is one complete row for each individual. So, if there is more than one contact at a company address, then the company name and address information is repeated. If you de-dupe on the basis of company name, then you are in danger of losing individual contacts.

My approach to de-duping has been to mix a programmatic approach with manual adjustments. The process is never easy. I believe you will find there is no easy way.

I work in Microsoft Access so that I can use queries as the engine room for the earliest sweeps. The first sweeps start with postcode/zip code as the target field. Then I work from there. On occasions I found the need to write text-handling functions with which to process some entries.

I make tables of the results of each major sweep so that I return to any point of the process if later on it is determined that things have gone wrong somewhere.

I aim to produce finally one table of company names and address and another table of contacts with a one-to-many link between the tables to associate individuals with companies.

Without exception I am left with records that have not passed throught the sieves and for which I have to ask the client what I am to do with them.

It is likely that you will not get the de-duping right to everyone’s satisfaction. So prepare yourself for the complaints.

On the up side, I am pretty certain that my services represented better value to the client than putting the work out to a bureau. By doing it yourself you will have complete control and will know where uncertainties have arisen during the de-duping/cleansing activity.

[Meandmymutt]

As above, it depends, it depends.

At the very simple level, you could always open the CSV in Excel, use the Data drop down, Filter, Advanced Filter.
On the Advanced Filter window, you can choose to filter your data in place, or copy to another location - and there's a tick-box (check-box) marked 'Unique records only'. Click it.
Select, or type in, the data range that you want to filter, then give a location for the filtered data ($a$1:$a$999 filtered to $c$1:$c$999).
Done
De-duped in one pass, and especially useful for email lists.

[Gtal]

Hello.

There has been some good advice on this thread and you should look at all possibilities before starting the de-duplication.

What I'd recommend is to have a look at an ETL tool. It allows, among other things, to "clean up data" including deleting duplicate records easily.

Also there are open source ETL tools, making it pretty easy to download.

[msduckland]

Cleaning up data can be a pain but is very neccessary when speed is required.