Aug 16 2022 11:54 PM
Aug 17 2022 01:27 AM
Aug 17 2022 02:35 AM
As usual, I would go with @Hans Vogelaar'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));
Aug 17 2022 03:22 AM
Aug 17 2022 03:59 AM
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) )
Aug 17 2022 08:20 AM
=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 ) )
Aug 17 2022 09:41 AM
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!