Match Names in different formats

[Starasta]

Hello!



I have two different sets of data and I'm trying to find a way to match the names between them which are very different in format. Examples below:

Sherry A Aaholm /// Aaholm, Sherry A. (Board)
Lawrence (Larry) P O'Reilly ///O'Reilly, Lawrence P. (Board)
Steven (Steve) J Strobel ///Strobel, Steven J. (Prior Board)

Google docs Link with the data set since it's over 500kb. Theres also unique entries without matches in the other data set. The ID's are also different since the data comes from different sources.

Data Set

I'm currently trying to solve the problem with basic excel formulas but I'm not sure how well that will go.

If anyone has any ideas on how to solve this issue I'd appreciate it.

Edit: I'm using office 365 if that matters.

[kilroyscarnival]

Haven't tried this out, but here's some stuff on how to use formulas to flip the name order. Of course, this might not work out with middle names, Juniors, etc.
https://www.contextures.com/excelsplitnames.html

There is also a fuzzy match option to merge data in Excel's PowerQuery. I haven't used that yet, but here's a video that might help: https://www.youtube.com/watch?v=aOl944MvkFc

If I have time I'm going to watch that tomorrow and try it on some lists I have that present similar issues. I just haven't had to do it yet.

Ann

[Starasta]

The link you sent first is the method that I'm currently using but the complexity is very different. I don't know how many differences in spelling there are between the two data sets therefore it's hard to match them through formatting.

But the second link you sent seems very promising. I've tried it and got around 6.000 matches out of up to 14.500 values. I haven't verified how accurate the matching is. But I think combining the two methods of aligning names first and using PQ after could lead to promising results.

Thank you for sharing!

Lukas

[Starasta]

After general formatting and aligning the names as much as possible with little effort I ended up with 8.6k matches out of up to 14.5k matches. This seems like a very good result to me since I don't know how many matches there are.

I checked about 100 values and they I have yet to find a mistake where PQ put two different names together.

[kilroyscarnival]

Originally Posted by Starasta

After general formatting and aligning the names as much as possible with little effort I ended up with 8.6k matches out of up to 14.5k matches. This seems like a very good result to me since I don't know how many matches there are.

I checked about 100 values and they I have yet to find a mistake where PQ put two different names together.

That's great to know!

I haven't had that issue with large sets of data yet.