I have this table : enter image description here

So I have the days column and fruit column. There's grouping based on days : Monday, Tuesday , Wednesday etc indicated by that grey and light blue color to separate each day. The fruits are grouped by the days.

I want make a single conditional formatting (for J1:J range) to flag if there's the same fruit on the same day. For example that red font color : 2 apple on Tuesday and 2 banana on Thursday.

if no grouping by days, i can use :

=countif($J$1:$J, $J1)>1

But how to seperate it if they are grouped by days column ? Thanks.

  • Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Jul 10 at 4:22

The easiest solution would probably be to use a helper column K where the day names are filled down, with this formula in cell K1:

      row(J1:J) <= max(row(J1:J) * (J1:J <> "")), 
          if( len(I1:I), { row(I1:I), I1:I } ), 

You can then use this conditional formatting custom formula rule:

=countifs($J$1:$J, $J1, $K$1:$K, $K1) > 1

You can hide the helper column K if it gets in the way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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