Forum Discussion
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 suggestion?
Thanks,
José
13 Replies
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 ) )
- Thanks
- Thanks, allways returns 00-01-1900
Are you sure you used the correct ranges?
{=MIN(IF($B$1:$B$10 = "John", $A$1:$A$10))}
array formula
Ctrl + Shift + Enter to complete the formulaAdded example file.
Hope I could help you with these information.
I know I don't know anything (Socrates)
Thanks NikolinoDE. Allways returns 00-01-1900
- Hi,
Pivot tables seem me to be the easiest way to go. If yo see zeros, there might be empty rows, or rows with only names.
Kind regards
Hans
