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

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress

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

Photo of David Quinonez

David Quinonez

  • 70 Points

Posted 2 years ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

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

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.