Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Using Access or Excel to identify duplicate records?

Jun 24, 2005 7:52AM PDT

Here is the problem: I have an excel database of 10,000 leads input by various salespeople--name, address, title, phone are the column headings, along with a column heading/field for the responsible salesperson.

It just so happens that many of our salespeople have the same contacts input into the database. The only difference between the records is the responsible salesperson. We estimate that approximately 5% of this database are dupes.

How do I use Excel or Access create a report or isolate just the duplicate records so our sales team can figure out how to clean up the database?

Thanks.

W!

Discussion is locked

- Collapse -
Re: duplicate records
Jun 24, 2005 8:11AM PDT

I would do this in Access (after importing the spreadsheet).

Make a group-by query (grouped on all fields except salesperson). Add a count field. Select if count > 1. Voila.

The problem, in practice, will be spelling variations and typo's (like McDonalds and MacDonalds and a lot of other ones); those aren't counted as duplicates. So some (I mean: a lot) of additional work lies ahead: sort on state, postal code, company name, person name and do a manual compare in printed reports. You'll never catch everything, but you could get a rather clean database given careful work.

In fact, I would never use a spreadsheet as a database used by different people to maintain their contacts. Consider to switch a simple multi-user CRM-system, so yoy can have shared contacts, shared notes, shared orders and shared everything between the salespersons.
But that's your choice, of course.

Kees

- Collapse -
Good Answer. I'll try it.
Jun 24, 2005 2:22PM PDT

Great reply. Thanks. For the record, the data was exported to an excel sheet for mailing purposes from a CRM program called Goldmine. For some reason, our sales dudes can't weed out their own dupes, so I thought I'd try and assist them.

W!

- Collapse -
Quick Solution
Jun 30, 2005 5:11AM PDT

The United States Postal Service can pull all duplicates from a list for the cost of $4.70 per/thousand. I just had this done through a mass mailing service. I can send you their email if you want to try this.