data cleansing once more

[Tomekkw82]

Hi Everybody,

I am supposed to do client analysis, howevere, I have a big problem with data quality starting with client's names

I am supposed to match our key accounts for the largest companies in our region. The official list contains full official names of the companies, e.g. John Smith Ltd.

What we have in our system is completely different. Examples:
John Smith
J. Smith
Smith

Any ideas how to do data cleansing for this?

 

[Joe4]

In any program, it is very difficult to do any sort of matching when the matching fields aren't exactly the same, as assumptions and "guesses" have to be made, which creates a high potential for errors.

Probably the best you can do is to use "Fuzzy Matching".
Have a look here: http://www.mrexcel.com/forum/showthread.php?t=69649

 

[bryonwoods30]

If it is something you have to do quite often you could make a table up for using a vlookup function. not sure if it is practical in your situation or not. something like below that you could keep adding to.

column a column b
John Smith John Smith Ltd.
J. Smith John Smith Ltd.
Smith John Smith Ltd.

You just do a vlookup on column a and it would always return back the actual name you want.