Find exact matches from multiple fields using batch macro

knozawa

Hello.

I would like to find exact matches using list of keywords.

Sample keywords:

Sample keywords.png

* In the real project, several keywords are more than one word (i.e. Cost effective or United States).

Sample Input:

Sample Input.png

Desired output:

Desired output.png

Matched mostly "OK" when I used FindString formula, but there are a few cases that was not exact match (highlighted with red):

Highlighted errors.png

I then tried to use Find Replace tool for exact match. It worked well in terms of exact match, but I could only append one match even if there are multiple matched keywords.

Find Replace tool matching.png

Is there any way that I can find exact matches from multiple fields using the list of keywords?

I attached:

- Batch filter multiple keywords test workflow that includes two macros:

- Batch filter multiple fields macro (using find string formula)

- Batch filter exact match macro (using find replace tool)

Thank you for your help in advance.

Sincerely,

knozawa

Batch filter multiple keywords test.yxzp

Tips and Tricks

Macros

Accepted answers

ddiesel

@knozawa how about this approach?

I padded the left and right of each input with spaces before it goes into your macro (which is very clever, by the way!). This works by replacing "apple" in your macro with " apple " to isolate it as a phrase. I added an example of "Cost effective" to represent a multi word phrase (see record #12 of text input (56) and record #10 on text input (57)). In your macro, "apple" is replaced with " Cost effective ". Note the spaces. The reason I padded the inputs for title, content, and abstract was to capture it if the phrase is at the beginning or end of the field. Lastly, I used a Cleanse tool to remove the leading and trailing white spaces that I created.

It is still case sensitive with this setup. See record 30 and 31 of Output (63) for "Apple" vs. "apple". I'm not sure if you want it to be case sensitive, but if not, the same logic of changing the case would work here.

Let me know if this works! Also, I'd love to hear if any of the Alteryx Aces have a more elegant solution. This was the only solution that came to mind.

All comments

ddiesel

Hi @knozawa

I think you can give the Find Replace method another try. This approach parses all the text in the three input fields by space to run the Find Replace tool on each word of the three input fields separately. Let me know if this is what you're going for.

Note: I made this case insensitive (see record 11 "apple Apple APPLE") by dropping a Cleanse tool after the Join tool, then used the Sample tool remove all the duplicates. I used the Cleanse tool one more time to put the "Keyword" back to title case.

This post is very similar to something @kavithabanda is working on here: REGEX-Match-function-to-match-subset

I wonder if you are working on the same project?

I attached the workflow. Hopefully this works for you both!

Batch filter multiple keywords test ddiesel.yxmd

knozawa

@ddiesel,

Thank you for your idea. Unfortunately, this method doesn't work well with my case. Apologies that I didn't include keyword with more than one word (i.e. Cost effective) in the sample keyword list. There are several keywords that have more than one word, so I don't want to split text to rows.

I didn't know about @kavithabanda's post, but it's interesting that some people have similar questions.

I wonder if there is any ways to do batch macro using Find Replace tool.

Sincerely,

knozawa

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703