Forum Discussion

DavidQuinonez's avatar
DavidQuinonez
Qrew Member
9 years ago

How to align multiple entries, similar but not exact, that are assigned to a number to become exact?

I am researching a program that would "sync" data that we have for a large amounts of data for one of out largest clients in the US. Specifically when I say "sync", there are multiple supplier names, identified with one supplier name (This is not to be the case, there should only be one number associated with one name but when you are dealing with suppliers that have several different offices in different countries, the number is identical, however the name will be entered with an extra character or an extra comma or will have "Inc." when it should be "Inc") This "sync" is a cleansing of the data so that we can create an accurate report working with Pivot tables in excel. However, when you are working with 1600 suppliers, you are talking about a solid week of 10 hour days.

That is what we are dealing with and I was wondering if your program could help us out with this. See the example below.



Thank you for your help, 


David

  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    9 years ago

    The more sophisticated developers will laugh at this, but hey, it works.

    You can upload all your data into one table and create a formula field for each errant character to be removed.

    The Remove apostrophe  Text formula is:

    Left([New Supplier-Contact Name],"'")&Right([New Supplier-Contact Name],"'")

    Notice that the formula above works the original hard data.


    Then, the Remove period Text formula is:

    Left([remove-apostrophe],".")&Right([remove-apostrophe],".")

    Notice that the remove period formula works the remove apostrophe formula. After you get the results you want, you can remove dups with Excel.

    Hope this helps.

    Chris

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Another solution is pure excel.
    Make a pivot table of unique Supplier IDs. or make a summary report in QuickBase of unique Supplier IDs.

    Copy those unique entries ot a new sheet.

    Use a vlookup in excel to lookup the supplier name.  It will find the first entry it find son the list and take that spelling as correct.