Word match in a list

Hello!

I am trying to match the occurrence of a particular word in a list of items. For example: In the attached spreadsheet, there are tabs - Product List (contains the product name and its ingredients), Search Words (each of these words should be checked in the ingredients list of each product), Results (results for each match should output as 1 or 0. Whole words should be matched).

If I use "contains" formula, it returns as "CORN" being present in "PEPPERCORN" which is not true as the whole word match is not done. Tried Regex_match, no luck.

Find and Replace tool does not output a row for each and every match word. Is there any other solutions? My output should be exactly what is in the Results tab.

Attaching the spreadsheet and the alteryx workflow.

Thanks in advance!

List_match.yxmd

Workflow

Regex

Fuzzy Match

Accepted answers

Claje

Hi,

Based on that requirement, I added a couple steps to my "Method 2" - attached the solution.

Basically, you can use Append Fields to put each of your ingredients you are looking for on a separate line for each product.
Then, you can use the "Method 2" approach to get only the ingredients (per product) which matched using a Summarize tool.
After that, you can join on Product and SearchTerm, and use a Union to get a "Left Outer Join". From there, you can rewrite your "Match" column to be a 1 if there was a match, and otherwise be 0.

List_match_claje_Updated.yxmd

All comments

kavithabanda

Spreadsheet attached.

match list - post.xlsx

Claje

Hi,

Do you only care about how many ingredients exist, or do you care about the specific ingredients that exist?

If the former, if you add a second column to your list of individual ingredients with a weird value in it (I used % as my example), you can replace each found ingredient using Find/Replace, and then use REGEX_CountMatches() in a Formula tool to accomplish this.

If you care about specific ingredients, and your list is always comma separated, Text To Columns + Find Replace + Summarize should do the trick. You can use "Split to Rows" in Text To Columns to get the dataset you want.

I've attached an example with both of these options

List_match_claje.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703