Fuzzy Match Last Name, then Fuzzy Match First name

[sfarnham1]

Hello,

I'm working with two lists of names looking for potential matches, but aliases may be used.

What I'm hoping to do is run through the list on just last names, retain those match scores, then with the remaining matched names check if first names match.

I don't want to check list 1 against itself, and I only want to check first names against the list of names that have already. Even if there is a 0% match on first name, I still need any entry that matched the last name to carry through to the final result.

Thank you in advance.

Ex output format

Full Name list 1	Matched Name (from list 2)	List 1 LN	List 2 LN	Last Name Score	List 1 FN	List 2 FN	First name score
Adam Smith	Elizabeth smith	Smith	Smith	100%	Adam	Elizabeth	0%
Adam Smith	Adam Smith	Smith	Smith	100%	Adam	Adam	100%
Allan Breny	Adam Brown	Breny	Brown	X%	Allan	Adam	Y%

[caltang]

Some relevant links:

• https://knowledge.alteryx.com/index/s/article/Nicholas-or-Nick-name-A-Guide-to-Tweaking-your-Fuzzy-M...
• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Fuzzy-Match-for-Names/td-p/772...
• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Complex-Fuzzy-Match-of-Company...
• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Fuzzy-Match/td-p/1127084 

[ddiesel · Accepted solution]

Hi @sfarnham1 !

Fuzzy matching is an art, and the resources @caltang provided are great!

I'm not quite sure I understand your logic (why does Allan Breny match with Adam Brown on line 3 of your sample output?), but I took a stab at it to help you get started. You will need two fuzzy match tools - one for the Last Name matches and a second for the First Name Matches. I'm not sure I joined everything back the way you intend, but please see the workflow annotations and adjust as necessary.

Hope this helps! Let us know if we can help further.

[caltang]

Fuzzy Match is an art, not a science. Love this line - great work @ddiesel !

[sfarnham1]

Hi @ddiesal 

Thanks for your help!

The first match is looking similar to what I have, but I'm having issues with the second match

Rather than splitting and matching them separately, I want to check last names first, then only within those that had a positive match check against first names. 

So for example, depending on threshold, Breny and Brown would have a positive match (I just made up names and didn't think too hard about the actual threshold for the example), I would then check their first names against each other. It is also okay for one person to match against multiple in the fuzzy match as there will be a final manual review of the results.

List #2 is the US treasury's Sanctioned Designated Nationals List. This is really about finding if there is any potential match that would be further reviewed, hence last name check first as it is a significantly more unique identifier than a first name

[sfarnham1]

Apologies, this is actually perfect. The issue came from my end.

Thanks so much for your help!