How can I highlight a max value in a column based on date criteria in corresponding rows.

Question

Hello, I have the following table of data with columns, "start_date", and "Total". If the date in column A is a single date, then I need to highlight the 'Total' (column B) cell for that row (or the entire row if that is easier). If there are rows of consecutive dates, I need to find the max value of those dates in the corresponding 'Total column' and highlight it (again either cell or entire row). For example...I put a box around the rows with consecutive dates to see better. I have anywhere from 5,000 - 15,000 rows of this data and in desperate need of help.

Thank You

Answer 1

I have assumed that your data is sorted by date, ascending.

This solution uses another column of formulas.

In C2, enter 1

In C3, enter the formula

=IF(A3=A2+1,C2,C2+1)

and copy down.

Then select all of column B (B2 to the bottom) and use Conditional formatting, New Rule, Use a formula to decide.... and the formula

=B2=MAXIFS(B:B,C:C,C2)

And choose you highlight color and press OK.

You can get a summary of the dates and their corresponding values in a table by using this Spill formula (which needs the third column of formulas):

=LET(d,HSTACK(FILTER(A:B,A:A<>""),VSTACK("Max",BYROW(VSTACK(1,LET(g,A3:A22=A2:A21+1,SCAN(1,g,LAMBDA(a,v,IF(v,a,a+1))))),LAMBDA(a,MAXIFS(B:B,C:C,a))))),VSTACK({"Date","Value"},DROP(FILTER(d,INDEX(d,,2)=INDEX(d,,3)),,-1)))

Answer 2

Hi,

In cell C2, type 0. In cell C3, enter this formula and drag down

=IF(A3-A2=1,C2,MAX(C$2:C2)+1)

Enter this conditional formatting formula in cell B2 and paste the formatting down

=$B2=MAXIFS($B$2:$B$16,$C$2:$C$16,$C2)

Answer 3

Since you are incrementing only on changes, and filling the previous value down when there is no change, the max value is always in the cell above the formula with the cell, so

MAX(C$2:C2)+1

is unneeded, and can be simply

C2+1

That may speed up calculation.