Forum Discussion

jlgomes's avatar
jlgomes
Copper Contributor
Aug 17, 2022

Earliest date

Hi,  I would like to get the earliest date from a set of data where per each person I've got more than a date. I´m struggling to get the unique name and the earliest date as shown above. Any...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 17, 2022

To add to this as a demo of modern Excel, the Lambda function could be reused to allow the person with the latest start date to be identified.

= LET(
  latestStart,  MAX(EarliestDateλ(UNIQUE(name))),
  FILTER(Input, date=latestStart)
  )

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 17, 2022

PeterBartholomew1 

As variant

=LET(
   s, SORT( Input, 2, 1),
   u, UNIQUE( Input[Name] ),
   HSTACK( u, XLOOKUP( u, CHOOSECOLS( s, 1 ), CHOOSECOLS( s, 2) ) ) )

and descending sort for the latest date. For the latest startdate

=LET(
   s, SORT( Input, 2, 1),
   u, UNIQUE( Input[Name] ),
   m, MAX( XMATCH( u, CHOOSECOLS( s, 1) ) ),
   CHOOSEROWS( s, m ) )

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 17, 2022

    SergeiBaklan 

    I would be happy to go with those!  It seems we have a surfeit of choice when it comes to selecting the best methods at the moment.  I suspect I tend to resort to thunks too readily and I still have to get to the bottom of the array shaping possibilities offered by TOROW/COL, WRAPROWS/COLS, SORT!

Resources