CODEWITHSUNDEEP
excelexcel-formulamaxpivot-table
racheljessica
racheljessica

Reputation: 23

Finding the maximum of multiple row "groups" in Excel

I am trying to find the most recent work date in different rows - original information is from a pivot. The number of entries in the rows differ each time and there are so many groups. I can't figure out a formula that I could drag down and it would work. I would just use MAX all the time but there's too much data. I only want the formula output to show up on the first row/column for each "set" - the result should be in the "most recent work date" column.

Example

Element Work Date Most recent work date (output I need)
JOE 123 2022-02-18
2022-02-19
joe 999 2023-03-19
2023-03-10
Cell 1 2024-03-12

I'm also putting in an image, that might make more sense

Thank you!

Example Image from excel

Using MAX formula doesn't work because you can't drag it down - I don't want outputs in every single row, just at the start. Doing MAX by hand doesn't work as there is too much data (don't have time, can physically do it)

Thank you!

Upvotes: 1

Views: 170

Answers (1)

VBasic2008
VBasic2008

Reputation: 55073

Retrieving Group Maximums

=LET(uData,A2:A21,vData,B2:B21,
    s,SCAN("",uData,LAMBDA(sRes,r,IF(r="",sRes,IF(r=sRes,sRes,r)))),
BYROW(uData,LAMBDA(r,IF(r="","",MAX(FILTER(vData,s=r,""))))))

enter image description here

To return an empty string instead of the 1900 date, you could use:

=LET(uData,A2:A21,vData,B2:B21,
    s,SCAN("",uData,LAMBDA(sRes,r,IF(r="",sRes,IF(r=sRes,sRes,r)))),
BYROW(uData,LAMBDA(r,LET(
    m,MAX(FILTER(vData,s=r,"")),
IF(r="","",IF(m=0,"",m))))))

Upvotes: 2

Related Questions