Correct text list fix or lookup against correct list

[blueroost]

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

 

[Akuini]

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]

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]

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.
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]

Your best bet is going to be either:

• Power Query's fuzzy matching
  Here is one "how to" from Jeff Lenning's Excel University
  Fuzzy Match with Power Query
• Excel Add-In for fuzzy matching
  To download from the microsoft site
  Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center.
  You may need your IT to install it on a work machine.

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]

Thank you, that fuzzy lookup is brilliant and versatile.

 

[Alex Blakenburg]

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.