Correct text list fix or lookup against correct list

blueroost

New Member
Joined
Jul 15, 2010
Messages
44
Hi
I have an exported list of names and dates in organisation. The names are different format, they can be full name or initial and surname or first middle and last name etc which means I have duplicates when using a pivot table.
Example for all same person
Bob Smith
B. Smith
Bobby Smith

My current fix is to sort alphabetically and drag down against each person with the first available name, then again with next person, so on and on, which takes time with several staff.

Is there a faster way to do this, i.e, either to correct or to lookup against Bob Smith on a master and match and replace as an example?

I have also looked at extracting surname and then doing a lookup with limited results.

Any assistance on fastest way to "cleanse the list" would be appreciated.

thanks in advance
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Example for all same person
Bob Smith
B. Smith
Bobby Smith

How do you determine that they are the same person? Is there any info in another column to conclude that?
 

blueroost

New Member
Joined
Jul 15, 2010
Messages
44
Hi, there is no others data to identify the person, they are a payroll list with no duplicate names hence knowing who they are. I thought of creating a master sheet of actual names to somehow try do a match but this did not work well.
Latest solution is extract initial and surname, again only as good as the software picks up handwriting to export to report


Solution
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Sorry, if you can't come up with criteria on how to identify which names belong to one person then I can't help you. :cry:
Hope someone else has better idea.

But I'm curious how this work:
My current fix is to sort alphabetically and drag down against each person with the first available name, then again with next person, so on and on, which takes time with several staff.
I'm assuming that in one column there are names of many different person, and each person might have multiple names with different format.
Are you saying if you sort the data then the names of a person will be next to each other?
Can you post an example with says 5 different person? so I can't understand it better.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Your best bet is going to be either:
It you will still need to manually go through and check whether you think its matches are reasonable.
I have a slight preference for the Add-In since it outputs the match probability, so you can set a much lower threshold then manually decide which match is appropriate.
You can generally accept anything over around 80%
 

RougeFaction

New Member
Joined
Aug 26, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Thank you, that fuzzy lookup is brilliant and versatile.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
Glad it looked promising, hope it helps.
If you can get your sources to include email address that is often helpful in matching since people tend to have preferred names and email addresses often help resolve these.
If you can get it from both systems it is also more likely to give you an exact match.
 

Forum statistics

Threads
1,147,734
Messages
5,742,861
Members
423,760
Latest member
photogfrog

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
Top