Fuzzy Match Group with punctuation

Question

Hello everyone,

I'm trying to run the Fuzzy match to sales data against a record of salespeople to standardize their names in the sales data. Due to titles, I need to keep some punctuation to match the Salesperson Master file. The data I'm receiving looks like this:

GroupKeyADAMS, JOHNADAMS, JOHNADAMS, JOHNADAMS, JOHN (MGR)ADAMS, JOHNJOHN ADAMSQUINCY ADAMSQUINCY ADAMS

And I would like the results to look like this:

GroupKeyADAMS, QUINCY (DIR)QUINCY ADAMSADAMS, JOHN (MGR)ADAMS, JOHNADAMS, JOHN (MGR)JOHN ADAMSADAMS, JOHN (MGR)ADAMS, JOHN

I suspect that I might be able to manage something with the Custom settings, but I'm not sure where to start. Any help is appreciated!

DannyS · Answer

@alexg1

thanks for sending the data! I went ahead and configured a fuzzy match that should meet your desired expectations. See workflow attached.

One thing to note is that fuzzy matching exercises do take some trial and error to configure the tool in a way that's best suited for your needs.

This was the set up I used for your use case:

Noticed I stripped punctuation for the beginning and used "alphanumeric only" to generate the match keys.

* Alphanumeric Only (Case Insensitive): Looks only at alphanumeric characters to make a match. Case is ignored.

I also checked off the box to generate keys for each word so JOHN ADAMS would be matched with ADAMS JOHN. More information on the fuzzy match editing window options can be found here: https://help.alteryx.com/current/designer/fuzzy-match-edit-match-options

Last thing to note is that in the Make Group tool, you'll notice that the group created for John Adams was "ADAMS, JOHN" rather than your desired "ADAMS, JOHN (MGR)" which makes sense since ADAMS, JOHN is the common denominator for the different possibilities of John Adams. Nonetheless, this was an easy fix with the formula tool to change that name to your desired output.

Please let me know if you have questions and hope this is helpful!

Best,

Danny

salespeople_fuzzy_match.yxmd

alexg1 · Answer

Of course! I've attached a file with sample data. This isn't the full data set, but a subset of reps with the same last name for testing. Names have been altered a bit for security reasons.

Fuzzy Match Sample Data.xlsx

DannyS · Answer

@alexg1

would you be able to provide the data before it goes into the fuzzy match tool / make groups tool so I can see all naming possibilities?