Aug 16 2022 11:54 PM
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 suggestion?
Thanks,
José
Aug 17 2022 12:08 AM
{=MIN(IF($B$1:$B$10 = "John", $A$1:$A$10))}
array formula
Ctrl + Shift + Enter to complete the formula
Added example file.
Hope I could help you with these information.
I know I don't know anything (Socrates)
Aug 17 2022 12:11 AM
Aug 17 2022 12:35 AM
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
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 ) )
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!