How to handle duplicate vendors

deb

Active Member
Joined
Feb 1, 2003
Messages
396
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
825
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,860
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,730
Messages
5,488,540
Members
407,643
Latest member
samerf86

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top