Filter by Partial String in a List

Hi SME,

I have a list that contains partial string that I want to look up against the raw data and filter out rows that contains the partial string. Attached is an example where the column C in the Raw_Data tab has the raw data with all kinds of string. I want to look up the table in the Lookup_Value tab to filter out anything in the Raw_Data tab that has the partial string in the Lookup_Value.

What is the most efficient way for me to do this? I could do this the long way but want to learn best practice.

Appreciate it.

Thanks,

kwl

Partial Value Lookup.xlsx

Preparation

Accepted answers

griffinwelsh

@knnwndlm see the attached solution

1. Use append Records to create a cartesian product

2. Use filter tool with formula Contains(trim(uppercase([Value])), trim(uppercase([TAX RATE LEVY])))

3. Summarize to eliminate duplicates.

Contains filer.yxmd

All comments

alexnajm

Sounds like a Find Replace tool to me! Raw Data into the F anchor, Lookup table in the R anchor, and find on "Any Part of Field". You can use the append option to add this column - if there's a matching value, a value will come through. If there isn't, it'll be null - this is where a Filter can then filter out those rows you need / don't need.

griffinwelsh

@knnwndlm see the attached solution

1. Use append Records to create a cartesian product

2. Use filter tool with formula Contains(trim(uppercase([Value])), trim(uppercase([TAX RATE LEVY])))

3. Summarize to eliminate duplicates.

Contains filer.yxmd

knnwndlm

@alexnajm - That was my initial approach but I kept getting null value.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703