Searching for duplicates in 400,000+ contact list

[attaboy000]

So I have been given a side project from my boss, with the goal of finding a better/more effective way of going through and identifying duplicate contacts in our database.

I am looking for some inspiration or ideas from anyone who has done similar work in the past. I know that Excel has a built-in duplicate function, which I have applied to 2 columns thus far: EmailAddress2 and EmailAddress3 in the hopes of weeding out a few duplicates and it has worked OK so far.

I'm curious whether there are some advanced functions to do this sort of thing (like using Index-Matching maybe?), and something that is flexible. The columns that I am working with are the typical ones: FullName, FirstName, LastName, Address, City, PostalCode, Company, EmailAddress1, EmailAddress2, PhoneNumber, MobileNumber, WorkNumber.

I realize that this may be a somewhat vague question, or may not even be possible in Excel, but I figured that I would at least throw the question out there. I'm by no means an expert in Excel so there's a ton of features and tricks that I am not aware. If there is anything that could help me in cross-referencing the various columns to weed out potential dupes, I'd love to hear it.

Thanks

[Bernie Deitrick]

You can also look at cross category duplicates using formulas, along the lines of

=IF(ISERROR(MATCH(H2,I:I,FALSE)),"Unique","Duplicated")

Where H2 is an email address, and I is a column of email addresses. If you have a lot of entries where the email is duplicated on the same line, change the formula to

=IF(OR(H2=I2,ISERROR(MATCH(H2,I:I,FALSE))),"Unique","Duplicated")

Copy that down - then delete any row where that formula returns "Duplicated" or then try to find the other row where the value is duplicated, using another 2 column of formulas:

=IF(OR(H2=I2,ISERROR(MATCH(I2,H:H,FALSE))),"Unique","Duplicated")

and

=OR(K2="Duplicated",L2="Duplicated")

of course, refer to the actual cells with the formulas above, then filtering to show TRUE based on the last formula.

[Bernie Deitrick]

Sorry for the duplicate - my initial posting failed, or so I thought.

[FlameRetired]

@ Bernie Dietrick

Not my thread, but thanks for those just the same. That part from OP's title

Searching for duplicates in 400,000+ contact list

caught my attention. Have been working out some not-so-slow helper column formulas for huge row counts. Not as easy as I thought.

Those are some good ideas I might be able to adapt.

Thanks again,

Dave

[FDibbins]

Another option might be COUNTIF()

=if(countif(range,a1)>1,"dup","")

[shg]

Sort by email address and look for duplicates in adjacent rows. That will be about a zillion times faster than a bunch of countifs.

[FlameRetired]

@ Ford

Yes. And thank you. That has been my 'go-to' formula in the past and has served well.

Then a few days back I posted this gem.

......50,000 row isn't that many [in reference to making a helper column]

Uh-huh and 640K ought to be enough for anyone. LOL Is my face red.

When I checked myself and used COUNTIF($A:A,A)>1 on 50,000 rows I was shocked how slow it was. Undo / delete and delete column was even slower.

Had to Task Manage --> End Task a few dozen times. Maybe part of that is my computer, but I have since found many of my other formulas (and thoughts) need overhaul.

That said, OP has an interesting and challenging problem here. Curious how it works out.

Edit Posted a bit late. shg does it .... again.