Office & Productivity Software forum

General discussion

Using Access or Excel to identify duplicate records?

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
You are posting a reply to: Using Access or Excel to identify duplicate records?
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Using Access or Excel to identify duplicate records?
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: duplicate records

In reply to: Using Access or Excel to identify duplicate records?

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.

In reply to: Re: duplicate records

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

In reply to: Good Answer. I'll try it.

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.

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

DEALS, DEALS, DEALS!

Best Black Friday Deals

CNET editors are busy culling the list and highlighting what we think are the best deals out there this holiday season.