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,
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:
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.