Vetting large contact lists against existing criteria

[Dylanz]

We are working with Excel sheets that have over 100,000 rows trying to compare and remove duplicates. The list is basically a contact list that has company name, email, and some contact data. We have a "do-not-call" list that we compare it to then remove the contacts that show up in the "dnc" list. The problem is a company may be formatted in several ways. For example, the list may contain, "Cogswell's Cogs" or "Cogswell's Cogs, Inc" or "Coswell's Cog Co." or "Chicago Cogswell's Cogs" and multiple other inconsistent variations.

Searching through so many rows in Excel is becoming prohibitive, especially when trying to filter based on fuzzy matches which requires multiple passes, and the list is only growing. We have tried using VLookups with wildcards, parsing and concatenating the data, but it's hard to filter because the variations themselves vary quite a bit. Sometimes the variations actually are different companies! So we've tried using the MS Fuzzy Lookup COM Add-in, but it runs very slowly. Not to mention we're not 100% confident in the results, though they seem to be pretty close.

I'm looking for a better way. I'm sure there must be one. Maybe the answer is 'not use Excel', in which case I'd be open to using other tools. Here's hoping that someone else has a similar experience they can share.

Thanks.

[Larena]

I fairly often come across the same requirement at my work. In my case it's checking whether companies in a list from an external source exist in our CRM. However, it sounds like you're already doing pretty much the same as me - multiple passes using partial names. There's always a lot of manual checking involved, but I don't think there's ever going to be a way around that - a human will understand that "Cogswell's Cogs" and "Cogswell's Dogs" are going to be different companies but Excel won't.

I do checking on as many of the other fields, such as email addresses, or just the domain name from the email addresses as I can. In Australia we also have an Australian Business Number (ABN), which is a unique identifier for the legal entity. If you have anything like that over there, and there's a way to get it included in the two sets of data that you're comparing, it is a huge help.

So, the short version of my answer is that I haven't found anything better than Excel, but if anyone else has I'd be glad to hear about it as well.