Pattern match text string against multi-select dropdown

eseymour

I have a column which has unstructured text - for example, a message from someone asking help in their own words.

I would like to match a row from another table based on one or more keywords.

In this table, there column with a drop-down list, if one or more of the items in the drop down list are present in the search text, then it should return that row.

I'm trying this out with has and contains, but can't seem to get it to match. To test, I've created a table with a single drop-down list column that I'm matching on the same row, the plan is once this is working, to wrap in collect / index / match type functions and reference another table.

But not getting the basics working yet.

HAS( [drop down]@row, contains(@cell,[search string]@row ))

I am expecting that if [search string]@row has at least one item from [drop down]@row then it should return true, otherwise false. But I only ever get a false return.

Is there any way to do this?
Ultimate objective is to pull through a value from a lookup table based on a match like this.

jmyzk_cloudsmart_jp

@eseymour

Here is an updated solution that does not use the keywords list sheet.

https://app.smartsheet.com/b/publish?EQBCT=945987aaf3924c24be559aac7e26344e

[ID] =IFERROR(INDEX({Search Text: ID}, Row@row ), "")

[Search Text] =IFERROR(INDEX({Search Text: Text}, ID@row ), "")

[K1] =IF(ISBLANK(Keywords@row ), "", LEFT(Keywords@row , FIND(CHAR(10), Keywords@row ) - 1))

[K2] =IFERROR(IF(ISBLANK([K1]@row ), "", MID(Keywords@row , FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 1)), FIND(DL#, SUBSTITUTE(Keywords@row + CHAR(10), CHAR(10), DL#, 2)) - FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 1)))), "")
[K3] =IFERROR(IF(ISBLANK([K1]@row ), "", MID(Keywords@row , FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 2)), FIND(DL#, SUBSTITUTE(Keywords@row + CHAR(10), CHAR(10), DL#, 3)) - FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 2)))), "")
[K4] =IFERROR(IF(ISBLANK([K1]@row ), "", MID(Keywords@row , FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 3)), FIND(DL#, SUBSTITUTE(Keywords@row + CHAR(10), CHAR(10), DL#, 4)) - FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 3)))), DL#)

[Has] =OR(CONTAINS([K1]@row , [Search Text]@row ), CONTAINS([K2]@row , [Search Text]@row ), CONTAINS([K3]@row , [Search Text]@row ), CONTAINS([K4]@row , Keywords@row ))

Paul Newcome

Are you able to provide some sample screenshots for context?

jmyzk_cloudsmart_jp

Hi @eseymour

Suppose your search text sheet is like the one below.

https://app.smartsheet.com/b/publish?EQBCT=bcae7ab19c9c4e298a55543e15b66849

Then, in the multi-select dropdown sheet that has keywords as a multi-select dropdown, like the one below, I added helper columns, L1 to L9, as the number of dropdown lists is 9, in this example.

https://app.smartsheet.com/b/publish?EQBCT=28ace98db499485e98829da4171a0171

I also prepared a separate dropdown list sheet.

The helper column, [L1], for example, is a checkbox column that gets checked if the first dropdown list, "Automation", is contained in the search text with the corresponding ID.

In the formula, "INDEX({Multi-Select Dropdown List}, 1))" is the first keyword, "Automation". By changing the row index, 1 to 2~9, you will get keywords like "Dashboard" (2) and "Workflow" (9).

So, the first IF checks if the selected [Keywords (Multi-Select Dropdown)] has "Automation", in the [L1]'s case, and if it has, checks if the corresponding search text, {Search Text: Text}, contains the keyword, and so, returns true.

[L2] to[L9] do the same thing, changing the row index.

Finally, the [Has] checkbox checks if one of the [L1] to [L9] is checked, and if checked, gives true, and [Has] will be checked, meaning that the corresponding Search Text contains one of the keywords.

In the example above, in the ID 1 case, since "Excel" and "Formula" are contained in the search text, the "[Has]" is checked.

In the ID 3 case, keywords like "Dashboards" and "Reports" are plural, but the search text contains singular dashboar and reports, the [Has] is not checked.

[Has] =COUNTIF([L1]@row :[L9]@row , true) > 0
[L1] =IF(HAS([Keywords (Multi-Select Dropdown)]@row , INDEX({Multi-Select Dropdown List}, 1)), CONTAINS(INDEX({Multi-Select Dropdown List}, 1), INDEX({Search Text: Text}, MATCH(ID@row , {Search Text: ID}, 0))))
[L2] =IF(HAS([Keywords (Multi-Select Dropdown)]@row , INDEX({Multi-Select Dropdown List}, 2)), CONTAINS(INDEX({Multi-Select Dropdown List}, 2), INDEX({Search Text: Text}, MATCH(ID@row , {Search Text: ID}, 0))))
[L3] =IF(HAS([Keywords (Multi-Select Dropdown)]@row , INDEX({Multi-Select Dropdown List}, 3)), CONTAINS(INDEX({Multi-Select Dropdown List}, 3), INDEX({Search Text: Text}, MATCH(ID@row , {Search Text: ID}, 0))))

This is another example that illustrates the necessity of the split function, which retrieves the individual item from a multi-selected dropdown list.

eseymour

Thanks @jmyzk_cloudsmart_jp yeah, I was trying to avoid a separate look up table.

Table 1 - column with unstructured text

Table 2 - lookup table for classifications

Table 3 - lookup table for the lookup table listing keywords for the classifications

The purpose of the multi-select box was to remove the need for Table 3

Really I want a variant of HAS
=HAS ( search_in , search_for ) ; is search_for in search_in
=CONTAINS( search_for, search_in ) ; is search_for in search_in

what I would like is something like
=MCONTAINS( search_for_range, search_in ) ; search_for_range - can be a multiselect - is anything in this range found in search_in?

jmyzk_cloudsmart_jp

Hi @eseymour

You can do without the table3, but in that case, the multi-select dropdown's split process becomes highly complex. Since we know what is in the multi-select dropdown, listing them in a separate sheet makes the splitting process much easier. (You can use the SUBSTITUTE function for the split process, for example.)

When I refer to the splitting process, I mean splitting a multi-select dropdown cell's items into separate values, for example, Excel, Formulas, and Functions.

We need to split the multi-select dropdown's items, as both 'HAS' and 'CONTAINS' can use the single 'Search_for' value, not a range. Therefore, if the multi-select dropdown includes Excel, Formulas, and Functions, you need to use the HAS or CONTAINS operators for those keywords individually.

eseymour

That works if the dropdown list is from a finite set of options. I was using it to collect an unbounded set of keywords for pattern matching. I did get it working with 3 tables, the final list of keywords was 82 rows, so unfortunately I don't think the column option would work.

Paul Newcome

@eseymour Are you able to provide sample screenshots of what you were referring to in your original post to include manually entered data showing the outcome?

eseymour

Table 1 is now the full list of key words (2nd column) with the primary key (1st column).

Table 2 is the primary table for the key words

I use the keywords (Table 1) to identify Headings from Table 2, and this is used to estimate 'types of work' from requests, see Table 3 (I am afraid I had to redact a lot of info here, but hopefully you get the idea - column 1 is essentially the content, column 2 shows the results).

Based on the results I also pull through the 'guidance' (column 2 of table 2).

Initially I wanted to avoid having Table 1, using a multiselect dropdown list that included the keywords. The dropdown would be unbounded, in that the list wasn't restricted to a specific set of selections.

Paul Newcome

What about screenshots of what you wanted? You mention an "unbounded" multi-select and not wanting to have to use Table 1? Can you provide screenshots of that with manually entered data?

jmyzk_cloudsmart_jp

@eseymour

Here is an updated solution that does not use the keywords list sheet.

https://app.smartsheet.com/b/publish?EQBCT=945987aaf3924c24be559aac7e26344e

[ID] =IFERROR(INDEX({Search Text: ID}, Row@row ), "")

[Search Text] =IFERROR(INDEX({Search Text: Text}, ID@row ), "")

[K1] =IF(ISBLANK(Keywords@row ), "", LEFT(Keywords@row , FIND(CHAR(10), Keywords@row ) - 1))

[K2] =IFERROR(IF(ISBLANK([K1]@row ), "", MID(Keywords@row , FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 1)), FIND(DL#, SUBSTITUTE(Keywords@row + CHAR(10), CHAR(10), DL#, 2)) - FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 1)))), "")
[K3] =IFERROR(IF(ISBLANK([K1]@row ), "", MID(Keywords@row , FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 2)), FIND(DL#, SUBSTITUTE(Keywords@row + CHAR(10), CHAR(10), DL#, 3)) - FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 2)))), "")
[K4] =IFERROR(IF(ISBLANK([K1]@row ), "", MID(Keywords@row , FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 3)), FIND(DL#, SUBSTITUTE(Keywords@row + CHAR(10), CHAR(10), DL#, 4)) - FIND(DL#, SUBSTITUTE(Keywords@row , CHAR(10), DL#, 3)))), DL#)

[Has] =OR(CONTAINS([K1]@row , [Search Text]@row ), CONTAINS([K2]@row , [Search Text]@row ), CONTAINS([K3]@row , [Search Text]@row ), CONTAINS([K4]@row , Keywords@row ))

eseymour

https://community.smartsheet.com/discussion/comment/488259#Comment_488259

Table 1 from the screenshots - 2nd column. 1st column is the primary key, 2nd column is an unordered list of keywords that isn't bounded (you can have as many as you like).

eseymour

https://community.smartsheet.com/discussion/comment/488335#Comment_488335

Thanks! I think that is technically how it can be done today in Smartsheets. Unfortunately I think it's more complicated than simply having a 3rd table. I appreciate you getting this working though - nice work.

jmyzk_cloudsmart_jp

Happy to help!😁