3

In Google Sheets I have two columns, A and B, and want to know when a cell in column A contains a word that is not in column B.

Column A contains cells with multiple words per cell, comma separated:

sarah, john, esther sarah, john yara, sarah

Column B contains one word per cell like:

sarah john esther

I want to know when a word in the first column is not in the second column. In our example, that would be yara.

Ideally that cell would go red, or another cell in the same row would show a warning.

1

You can do this by applying to range A1:A the conditional formatting with custom formula

=counta(unique(transpose(split(A1&", "&JOIN(", ",B$1:B),", "))))>counta(unique(B$1:B))

Explanation.

  • To the right of the inequality sign, counta(unique(B$1:B)) counts the number of distinct words in column B.
  • On the left, A1&", "&JOIN(", ",B$1:B) concatenates A1 with the content of column B, comma-and-space separated.
  • Then split(...,", ") separates all of this into a row of words
  • Then counta(unique(transpose(...))) counts the number of distinct words among these.
  • Formatting is applied if the second count is greater than the first.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.