Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2022, 12:29 PM
Starasta Starasta is offline Match Names in different formats Windows 11 Match Names in different formats Office 2021
Novice
Match Names in different formats
 
Join Date: Aug 2022
Posts: 3
Starasta is on a distinguished road
Default Match Names in different formats


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.
Reply With Quote
  #2  
Old 08-08-2022, 02:51 PM
kilroyscarnival kilroyscarnival is offline Match Names in different formats Windows 10 Match Names in different formats Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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
Reply With Quote
  #3  
Old 08-08-2022, 03:24 PM
Starasta Starasta is offline Match Names in different formats Windows 11 Match Names in different formats Office 2021
Novice
Match Names in different formats
 
Join Date: Aug 2022
Posts: 3
Starasta is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 08-08-2022, 04:13 PM
Starasta Starasta is offline Match Names in different formats Windows 11 Match Names in different formats Office 2021
Novice
Match Names in different formats
 
Join Date: Aug 2022
Posts: 3
Starasta is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 08-22-2022, 10:09 AM
kilroyscarnival kilroyscarnival is offline Match Names in different formats Windows 10 Match Names in different formats Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by Starasta View Post
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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match Names in different formats Removing microsoft build-in style names - display of style names in styles gallery changed Firebody Word 8 03-06-2022 08:22 AM
Match names with different formats franz Excel 3 07-21-2018 11:41 AM
Excel - Index and Match Function ,First Second and Third Match paulzy95 Excel 10 09-29-2016 10:46 PM
How to enter names in Resource Pool/names pstein Project 1 03-26-2012 07:37 AM
TOC formats ruggb Word 3 03-09-2012 02:07 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft