How to handle duplicate vendors
Results 1 to 4 of 4

Thread: How to handle duplicate vendors
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    392
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to handle duplicate vendors

    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.

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    193
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to handle duplicate vendors

    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.

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,673
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to handle duplicate vendors

    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 by Micron; Aug 22nd, 2019 at 11:31 PM. Reason: clarification
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Posts
    392
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to handle duplicate vendors

    Thank you both, for your input.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •