1

Each of the columns in my Google Sheet represent a farm. Each farm is a list of animals. I want to highlight the entire column if the farm has a duck, a horse, and a cow. So the column may look like this:

Duck

Horse

Cow

Potato

Octopus

And I want the entire column, including Potato and Octopus, to be highlighted. If the column did not have Horse, I would want none of them highlighted. I know I can do this with a massive list of AND and ORs, but there has to be a better way!

Edit: Research efforts: Googling any combination of these words gives me things I don't want. It tells me how to apply a conditional format to an entire column - but what it means by that is that it will highlight cells in the column that match X, not that it will conditionally highlight the entire column based on values inside that column. I've found a few things that work, but they're either really complicated (a series of AND/OR) or not really clear on why they work.

3
  • Welcome. Please add a brief description of you search / research efforts as is suggested on How to Ask. Commented Aug 9, 2019 at 0:05
  • 1
    if the animals are always in the same order, you can do a single check with concatenate or textjoin and regexmatch.
    – pjmg
    Commented Aug 9, 2019 at 10:45
  • @Andrew can you provide a sample ? Or an idea of how many conditions/animals needs to be tested ?
    – pjmg
    Commented Aug 10, 2019 at 7:56

2 Answers 2

0

Example for a column with 100 itens (row 1 as header row + 100 rows for farm things such as "cow" and "potato"):

  1. At the cell A1, add this formula: =countif(A2:A101,"duck")*countif(A2:A101,"horse")*countif(A2:A101,"cow")
  2. Select the cell A1 again, then go to Format > Conditional formatting and set Apply to range A1:A101, then Format cells if... Custom formula is =A$1>0, choose the formatting that you wish (e.g. yellow Fill color)
  3. Once again select the cell A1, then go to Format > Number > More Formats > Custom number format, then type "FARM 1" (yup, use the double quotes) and click Apply.

Now add random words to cells A2, A3 et cetera (until cell A101). You'll notice that if you add duck, horse and cow at least 1 time each, the entire column will change according with the conditional formatting that you preset on step 2 above.

How it works: the formula counts how many times the word "duck" occurs in the range A2:101, then multiplies the result by the number of times that the word "horse" occurs in the same range, then multiplies the result by the number of times that the word "cow" occurs in the same range. Therefore, if at least one of these 3 words is completely absent in the range A2:A101, the counting of such word will be zero, thus causing the result of the entire multiplication to be zero. And the conditional formatting only formats the column if the result of such multiplication (at the cell A1) is bigger than zero.

If e.g. you add the word duck 5 times, then add the word horse 20 times but don't add the word cow, the formula will calculate 5 x 20 x 0 and therefore will store 0 at the cell A1.

0

@pjmg had a great idea of using TEXTJOIN with REGEXMATCH.

=REGEXMATCH(TEXTJOIN(,1,SORT(A$1:A$100)), 
  JOIN(".*?",{"Cow","Duck","Horse"}))

Explanation

  1. REGEXMATCH's text argument is returned by applying TEXTJOIN to the sorted values from column A while skipping blank rows.
  2. The regular expression argument is created by joining {"Cow","Duck", "Horse"} with .*? as the delimiter which effectively means that there can be anything or nothing between the words in the text argument
  3. This scales easily as the words can be replaced by a range reference, and if they are not already sorted the reference can itself be wrapped in a sort to do it for you and optionally TEXTJOIN to remove blanks.

    For Example:
    =REGEXMATCH(TEXTJOIN(,1,SORT(A$1:A$100)), 
     TEXTJOIN(".*?",1,SORT($F$1:$F$10)))
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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