Searching names that match between two columns

[RajBala]

Hi, I am looking for a solution to match any names in first column (delimited by semi column) to that of the second column. Any help appreciated.

		Expected result
Remitter	Receiver	Any Name matched?
Jack Ma; John Poh; Robert Fan	John Poh	Yes
Jack Ma; John Poh	Jack Smith	No
ABC Limited; Asia Company	Asia Company; Africa Company; US Company	Yes

[andyuttley]

Hi @RajBala 

As there's multiple names per row, I'd use a text to columns tool to split it out (into one name per row) and match from there. Example attached:

Hope that helps

Andy 

[JoeS]

I have attached a process whereby I split the names out in you remitter column to multiple rows. Then ran a search, combined back the max result, so if there was a true that is what is attached back.

[estherb47]

A slightly different approach avoids using joins, so might run a touch faster.

To compare the receivers one at a time against the remitters, we need to separate multiples into single instances. Used the Text to Columns tool, separating into rows, and then a Data Cleansing to remove leading spaces

A formula tools uses Contains to figure out if receivers matches to the remitters.

Build back the original number of rows with a Summarize. Then another formula tool checks if there are any "yes" in a multiple receiver row. If so, a "yes" is the final answer.

Please let me know if this works.

Cheers!

Esther

[RajBala]

Thanks for your help, however one issue though:

Contains formula does not match exact names. So "Jack Ma" is contained in "Jack Mason" and therefore it returns "Yes". In this case I want it to return "No" as "Jack Ma" is not exactly same as "Jack Mason"

[JoeS · Accepted solution]

I have modified mine to check they are exactly equal, case sensitive as well

I split out the second column "Receiver" to multiple rows as well, allowing me to check exactly rather than search a contains.