I am working on a problem where I need to compare the count of names in 2 different reports and then final result will be the common in the both reports.
Now the issue is that in 1 report - There are 2 columns as first name and last name, which I merged into 1 using formula with column header full name.
Also 1 name can be repeated multiple times as per the nature of report.
The other report has direct full names but their full names also contain middle names, if any.
So some names match perfectly while others do not. Wang yi And wang yoon yi are the same in both reports.
So I created a workflow using formula (for full name), Unique (To filter duplicates), Join (To get perfectly matching names straightaway), then record ID, Formula for source, union tool and then fuzzy match on certain threshold like 60-70%. But not getting perfect result.
Also the final fuzzy match tool give some weird results. Even for some of the perfectly matching names it gives [null] in match score.
And when a name is coming only twice in the union, 1 from file A (Wang yi) and 1 from file B (wang yoon yi), it creates 5-6 records but the value remains null for all records.