Word match in a list

[kavithabanda]

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!

[kavithabanda]

Spreadsheet attached.

[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

[kavithabanda]

Claje,

Thanks for giving the possible solutions. What I am after is close to your solution of counting the occurrence of the match words in the ingredients but the ingredient list should not be split. Please take a look at the results tab in the XL I'm attaching to this reply. This solutions will be integrated in much a larger workflow so getting results in this way only would help me integrate without making changes in the larger workflow. 

Thanks!

[Claje · Accepted solution]

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.

[kavithabanda]

Claje,

This is almost what I need. I was looking at the original formula in my workflow and it is as follows. Since we are using a Find and Replace Tool and not a formula, how best can we add the condition in red below?

IF (Contains([List of Ingredients],[Search Words])
AND !Contains([List of Ingredients], "ORGANIC " + [Search Words]))
THEN 1 ELSE 0 ENDIF

[Claje]

I think if you update my formula as follows:

IF !ISNULL([Match])  AND !Contains([List of Ingredients],"ORGANIC "+[Match])THEN 1
ELSE 0
ENDIF

That should accomplish your goal.  Let me know if you get unusual results as I did not test this.

[kavithabanda]

Thank you Claje, this should work. I am going to inject this in the larger workflow and see how it works. Will get back soon.

[kavithabanda]

Thanks for helping Claje, this works.