Filtering rows based upon values in an array or rows

[randomfocus]

Hi - I'm new to this forum so first, thanks for being here!


I have a column of data containing various text strings in each cell (colA), I want to evaluate these strings to see if any words from another list specified in another column (ColB) occur anywhere in the value of the first column. If the current row in ColA contains any of the values from any row in ColB I want to return the value from ColA into ColC.


I have an example spreadsheet that explains this rather better than the ramblings above but don't know where or how to upload it :-(


Thanks for your help (in anticipation)


Regards

Randomfocus

 

[SirJB7]

Hi, randomfocus!


I'd suggest you to read the three green sticky topics at this forums main page. There you'll find guidelines about introducing you to the community, posting files and best practice rules.


Regards!

 

[Luke M]

Hi randomfocus!


In answer to your question, I think this formula will answer your needs.

=IF(SUMPRODUCT(1*ISNUMBER(SEARCH($B$2:$B$10,A2))),A2,"")


Where B2:B10 contains the list of words/phrases you are looking for. Note that none of the cells in B2:B10 can be blank.

 

[randomfocus]

Thanks SirJB7, I read the Sticky posts and was just about to Dropbox my example when Luke M gave me the solution! You guys are awesome, thanks a million!

 

[SirJB7]

@randomfocus

Hi!

Glad you solve it, and welcome back whenever wanted or needed.

Regards!