Fuzzy Match Reporting

Question

I’m puzzled on how to report some fuzzy match data.  I know what I would like to do, but I don’t know what the term is to search for.  I've attached the Excel file of my fuzzy match results (without Name/Address data as I show below)...

What I would like to do is this:

Display the UniqueID Record on one row in Excel, then display the UniqueID2 records, and their match scores, on subsequent rows in Excel, then rinse/repeat for the next UniqueID record.  Essentially:

UniqueID1           Name                   Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID1           Name                   Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID1           Name                   Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

And so on…

I know I can get the Name/Address data from using joins; however, I want to format the output in the way I'm showing above.

Does anyone have any ideas?

Todd-Test.xlsx

danilang · Accepted Answer

Hi @Todd_Sturner

I think this is what you're looking for.

It uses a couple of Multi-Row Formula tools to number the UniqueID1 with a gap of 100 between each and then number each UniqueID2 sequentially within each group.  Use a select tool to rename the UniqueID2 and ID2 fields to UniqueID1 and ID1 and a Summarize tool to get the unique list of UniqueID1s.  Union the 2 datasets and sort on ID1.

the results look like this with a record for the UniqueID1 value, followed by one record record for each of the matching UniqueID2 rows

Dan