Saturday, December 28, 2013

yogi_Pull Phrases (Sentences) From Column A That Contains Words or Phrases In Cells B2 to B (open-ended range)

                                          Google Spreadsheet   Post  #1456
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28, 2013
question by sailingonsound (http://productforums.google.com/forum/?zx=l86ee95dkrjv#!category-topic/docs/spreadsheets/xpqHOI1JGIQ)
Searching a column against a column to generate a column
I think this is pushing right to the very edge of what is possible to do in google docs (without scripts), I'm about to write code to do this but thought it might present a great ultimate challenge for the formula pros :)

A1: Title
A2: Snow White and the Seven Dwarves
A3: Beauty and the Beast
A4: Snowey snowtrack the snowmobile
A5: Snow Beasts and Dwarves
A6: A Snowy Christmas

B1: Find Words
B2: Snow
B3: And

C1: Result Sentences
C2: Snow White and the Seven Dwarves
C3: Beauty and the Beast
C4: Snow Beasts and Dwarves

So essentially the function would find all the sentences in Col A that contain any of the words in Col B and spit them out in Col C. It finds sentences that contains words for kids learning english, so if we know a kid is struggling with a few words we can find really good sentences to challenge them with. 

I was thinking that before doing a comparison you could add a " " to the end of the words, then add a " " to the end of the Sentence Col as well so it doesn't misfire on "Snowy" and the like but the array function feature is mystifying me. The closest I got was this
=SORT(UNIQUE(FILTER(B2:B;COUNTIF(UPPER(A2:A);UPPER(B2:B))>0)))

But it can only compare words to words, it'd be like that but with a search/find feature.
----------------------------------------------------------------------------------------------------------------------------------------