Google Sheets - Compare Two Cells And Display Unused Words

1
votes

My question is similar to this one, with a key difference in the output that I've no idea how to do: Google Sheets - Compare multiple words in cell with another cell & count number of matches

If I have a comma separated list in one cell and human input in the other using those words to write a sentence, is there a formula I can use to compare the two cells and display any words that haven't been used?

For example:

G8 (List): compare, cost, energy, glazed, glazing, guide, home, homebuilding, it, make, prices, range, renovating, savings, sense, triple, ultimate, window, windows, worth

G9 User Input: Is Triple Glazing Worth The Cost When Compared to Double Glazed Windows?

G10 (Unused Words): (Each word in G8) - (each word in G9) = leftover words

Is this possible?

3
google-sheets

3 Answers

1
votes

Alternatively, also try

=join(", ", filter(split(G8, ", "), not(isnumber(search(split(G8, ", "), lower(G9))))))

and see if that works?

0
votes

Use this formula :

=TEXTJOIN(", ",TRUE,FILTER(ARRAYFORMULA(IFERROR(FIND(split($G$8, ", ",FALSE,TRUE),LOWER($G$9)),split($G$8, ", ",FALSE,TRUE))),ISTEXT(ARRAYFORMULA(IFERROR(FIND(split($G$8, ", ",FALSE,TRUE),LOWER($G$9)),split($G$8, ", ",FALSE,TRUE))))))

What a mess. Well, at least, it works the way it is intended to do. It's hard to explain here, so if you don't mind, take a look at this sheet that I built. I explained the detail of the formula in there https://docs.google.com/spreadsheets/d/1K99EMtG8Bg6cuxz_bAyK60HJjmK1QlXmjQBwoxQYi5c/edit?usp=sharing

0
votes
=REGEXREPLACE(G8,JOIN("|",SPLIT("(?i)"&G9," ")),)
  • Add case insensitive flag
  • SPLIT G9 by space
  • JOIN the array back by OR operator |
  • Use the resulting string to REGEXREPLACE G9