Normalizing customer addresses and merge/purge functionality

Hello, new user here but I’ve used the site as a resource in the past. Thanks for the valuable information here.

We operate on Nav 5 currently and I’m wondering if any of the forum members can recommend a third party add-on or service that would provide address normalization/standardization and we also need the capability to merge/purge duplicates.

Any recommendations would be appreciated. Thanks.

Shane

Do you mean address data in Contacts/Clients/Vendors? Those are distributed over separate fields, although one can create mess in Address & Address 2 fields…

To avoid duplicates as XYZ Ltd / Xyx ltd / X.Y.Z. Ltd usually a small mod is done during implementation warning you that entity with such Reg # / VAT # / ID is already present (this is not included in standard functionality).

I understand your NAV hasn’t such checking - to find duplicates export to Excel full list of, say, Clients, sort them by Reg# and ask Excel to find duplicates, this is available IMHO from ver 2007 in Excel. However, if Reg# field happens to be not filled in, then only going through this list manually can help you…

Modris, finding duplicates using Excel is rather inefficient and will only work if the the match is exact. I need to find duplicates using fuzzy logic so that misspellings and abbreviations are taken into account. Thanks for the feedback though.

Anyone else have a solution or recommendation?

Shane

Well, I doubt such tool may exist, as even "fuzzy logic so that misspellings and abbreviations are taken into account" won’t help you for 100%, and algorithm will be complicated. There was SOUNDEX function in Excel, but I don’t see it anymore in 2010 / 2013 versions.

Don’t you really have such thing as company registration number in U.S.? Here in Europe we can spell the company name in most awkward ways even on bank documents, they IGNORE it and look only at registration no, which is unique and every company has it, if it is a person, (s)he has an ID instead (like every American has social security no).

No, companies are not required to provide a registration number of any sort in the U.S. Even so, the majority of the customers in our system are not businesses, they are individuals.

In my experience with other systems, there’s a matchcode that’s used. Comprised of several segments of fields and done in a way that it mitigates misspellings and such. First, you normalize the addresses so that North is abbreviated N, Street is St, etc. Then you take, for instance, the address house/building number + the first five characters of the street name + the postal code + the first 4-5 letters of the last name. You remove the vowels, too. This gives you a unique identifier that can be used to match duplicates. That’s just one method and an example. I’m pretty sure SAP uses this type of methodology and I’ve used another order fulfillment software that uses matchcodes.

Shane