CodePudding user response：

Paste this: formula in C1, to get a helper column that can be hidden.

=AND( A1<>"", LOWER(B2)= "ignore")

Paste this: formula in conditional formatting and set Apply to range to A1:A1000, take a look at

CodePudding user response：

This is essentially the same as Player() only a little shorter formula.

=if(A1<>"",len(SUBSTITUTE(TEXTJOIN("",,B2
:INDEX(B:B,MATCH(true,isblank(B2:B),0) row()-1,1)),"Ignore",""))=0,"")

Explanation of Dynamic Range

The hardest part of this is matching the groups of values in column b. To do this, I used a vector approach of with an index function separating the ranges with a :. So like one would do B2:B3, one could do: B2:Index.

To get the lower position, I used a method of matching the first blank (note ="" won't work). This will identify the distance from the cell the function is being called from. We then need to add the row it's being called from, then one cell higher (less) as we don't want the blank cell, but the one above. So combining... INDEX(B:B,MATCH(true,isblank(B2:B),0) row()-1,1) gets the dynamic lower value.

After that, there's a variety of ways to solve. I used textjoin and substation to confirm a length of zero as a method, but lots of other ways.