How to handle duplicate vendors

[deb]

Need your advice on the best way to handle vendors that are the same just called differently in the data I receive. One data source may call the vendor something different than another data source. I.e. abc co. Vs abc company. Or St Joseph’s vs Saint Joseph’s.

What is the best way to handle these? My data is auto imported from many departments and this is a problem since the vendors do not have a unique identifier.

HELP MEEEEE... please.

 

[Toadstool]

Hi Deb,

I've seen this a few times and there's no easy solution.

1. You can use some tool, such as the Fuzzy Lookup addin, but that's not 100% (as the name suggests) and when you get your next batch of files you'll have to do it all again.
2. You can resolve the differences manually and create your own Master Identifier (e.g. Sales call it ABC Co, Accounting calls it ABC Corp and Marketing calls it just ABC. You pick one and call it your Master Id then the next set of files you use lookups to replace their Vendor name with yours so you can complete your analysis). This works OK except when a department changes or adds a new name.
3. Have new Vendors created by a single Department who decide on the name. Usually this is Legal or Accounting as they'll be checking contracts, doing credit checks and checking payment terms so will want make sure everybody is talking about the same Vendor. As you don't have a single central system then this may be the best route but it needs Senior Management buy in and some kind of disincentive for people to create their own names.

 

[Micron]

I'm not sure if the following is the same as 2 above, but I don't think so: Without knowing what the source data or the db table(s) looks like I'll say that if you created a table of "synonym" names you could update the name field with values from that table. That is, if your query finds ABC Corp it updates it to ABC Co. However, I foresee a lot of looping through "proper" values and comparing them to source data. Such an operation might take so long that it would best be done after hours (automatically).

There is another thing called the Levenshtein distance which is used to find close matches between words, but it results in more of a suggestion based on the distance value you set. Thus with the appropriate distance value it should find appple when you compare it to apple, but it may not find apl. Whatever the results are, it still would require you to make manual determinations. I throw it out here just in case it would work for you. There are code examples using the technique but I suspect they will be daunting should you decide to research the suitability of it.

As always, the best fix is to get some control over input in terms of consistency. If you can translate into hours and money spent the work you are routinely faced with, you might get some buy-in for fixing the issue at the source. My take would be that if I could not convince management to induce some control, then they are happy to pay me to fix other people's carelessness.

 

[deb]

Thank you both, for your input.