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 resultRemitterReceiverAny Name matched?Jack Ma; John Poh; Robert FanJohn PohYesJack Ma; John PohJack SmithNoABC Limited; Asia CompanyAsia Company; Africa Company; US CompanyYes

Searching names that match between two columns

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

Regex

Tips and Tricks

Expression

Accepted answers

JoeS

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.

SearchingNames.yxmd

All comments

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

SplitAndMatch.yxzp

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.

SearchingNames.yxmd

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

SearchMatch2Columns.yxmd

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"

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703