Formula that would look at the keywords for that row

System

This discussion was created from comments split from: Add JOIN Function to Index/Match.

PDunn

I have a similar thing I am trying to achieve. I used the AI formula builder, but getting invalid date type error

=INDEX(Keywords@row, JOIN(COLLECT([Bullet 1: Overall style and item description]:[Bullet 5: Assembly instructions], [Bullet 1: Overall style and item description]:[Bullet 5: Assembly instructions], ISBLANK(@cell) = false), ", "))

Ask is:

1. Can you set up a formula that would look at the keywords for that row
2. Count any KWs that appear in the bullet copy columns 
3. Add a column and call it: Keyword Count
4. It shows how many of the KWs have been used in the 5 bullet fields
5. Our goal is to hit a minimum of 10 KWs in our copy
6. Conditional formatting to color the cell red if the count is less than 10, green if it is 10 or more

columns

Genevieve P.

Hi @PDunn

Will you always have the same keywords for all rows? If so, we could build out a long nested IF formula that would search for each individual word and if the cell contains each of your keyword phrases.

For example:

=SUM(IF(CONTAINS("Century Chair", [Bullet 1: Overall Style]@row), 1, 0), IF(CONTAINS("Accent Chair", [Bullet 1: Overall Style]@row), 1, 0), IF(CONTAINS("Living Room Set", [Bullet 1: Overall Style]@row), 1, 0), IF(CONTAINS("Velvet", [Bullet 1: Overall Style]@row), 1, 0))

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

PDunn

Hi @Genevieve P. Not all rows will have the same keywords, it varies based on type of product for that row. I will build this out and let you know how it worked. I have 59 keywords I need to search

PDunn

Hi @Genevieve P. I have all the keywords on another Smartsheet can I use a find match formula as another option? I have 59 keywords to match

Genevieve P.

Hey @PDunn

Thanks for clarifying!

There currently isn't a direct function or straight forward way in Smartsheet to compare two cells with text to see how many key words appear in one based on what's listed in the other. We would need to search for each individual phrase or element separately within the text in order to identify if it appears or not.

Even with 59 possible options, the only way I can think to achieve your goal is to write a long nested-IF statement that searches for each one and returns a number.

If you have a column in your sheet that identifies the Type of Product, then we can set up the IF statement to check for each product's keywords, like so:

=IF([Type of Product]@row = "Product 1", SUM(IF, IF, IF, IF), IF[Type of Product]@row = "Product 2", SUM(IF, IF, IF, IF)

And so on. Does that make sense?

Kelly Moore

Hey @PDunn

As @Genevieve P. pointed out, the only way using the native smartsheet functionality is a very long IF statement. If you have access to smartsheet's premier app Bridge, I have a routine that would loop the keywords through a Contains junction and update the key word count incrementally. Depending on your process, you might be able to do all the bullet columns in one workflow - or, it's straight-forward to build a workflow for each bullet column if they are updated independently of one another.

If Bridge is an option for you, or if you're just interested, let me know and I'm happy to post the Parent/child workflows.

Kelly