Lookup table containing single words to be matched with each row of comments field in file

I'm trying to find a solution to do a lookup function. I have a lookup table containing around 50 rows of words/phrases which need to be checked to a file containing around 300 survey responses open end answers. I initially used the find and replace tools but it will only find the first match of the lookup table row in the comment and stop. I want for each of the survey response open end all the 50 words compared to find a match. Please let me know if there is any easy way to handle this.

ex :

Lookup table :

1.BAT

2.MAT

3.CAT

4.SIT

Comments:

1.I have a bat with my cat

2.I sit on a mat with my bat

Current output using find and replace too (I used option append fields to record)

1.I have a bat with my cat -BAT

2.I sit on a mat with my bat -SIT

Expected output:

1.I have a bat with my cat -BAT,CAT

2.I sit on a mat with my bat -SIT,MAT,BAT

Thank you in advance for any help!

Developer

Developer Tools

Fuzzy Match

Preparation

Accepted answers

estherb47

Hi @chanikya

What about this approach?

You can append all of the words to each sentence, and then calculate if there is a match using Contains. I tagged all containing with the number 1. Filter out the 1s, and use Summarize to concatenate the list of matching words.

Let me know if this works.

Cheers!

Esther

FindWordMatches.yxmd

All comments

danilang

Hi @chanikya

Try this technique.

Split your comments into individual words and match these to the look up words. Concatenate all the matches grouped by RecordID and join back to the original comments

Dan

Matches.yxmd

chanikya

danilang

I did try this technique. Practically this won't be efficient because I work with hundreds of responses and each response is around a 100 words at least.The file which has comments also have multiple fields in it. To add to that i also have some cases to look for phrases in the text. It would be secondary to get to phrases but I would at least try to find a effective solution for single words. Thank you for putting this solution together.

Please let me know if it makes sense. Real time data is more complex and difficult to handle using this approach.

Chanikya

estherb47

Hi @chanikya

What about this approach?

Let me know if this works.

Cheers!

Esther

FindWordMatches.yxmd

chanikya

Thank you @EstherB47. This solution works fine even for phrases as well as single words. I will repost if I run into issues. Appreciate all your help!

Chanikya

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703