Forum Discussion
Earliest date
As usual, I would go with HansVogelaar's solution.
The first step might be "If you are using any spreadsheet software other than Excel 365, bin it and start fresh!"
Packaging the solution into a single formula, you might have
= LET(
distinctNames, UNIQUE(name),
ealiestDates, MINIFS(date,name,distinctNames),
HSTACK(distinctNames,ealiestDates))If your focus were on the further use of the earliest dates as opposed to merely displaying the result, you could define a Lambda function
= EarliestDateλ("John")
where
EarliestDateλ
= LAMBDA(n, MINIFS(date, name, n));
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) )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 ) )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!
