data cleansing once more

Tomekkw82

New Member
Joined
Apr 2, 2010
Messages
15
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top