New Contributor

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

Re: Earliest date

{=MIN(IF(\$B\$1:\$B\$10 = "John", \$A\$1:\$A\$10))}

array formula
Ctrl + Shift + Enter to complete the formula

NikolinoDE

I know I don't know anything (Socrates)

Re: Earliest date

In D1:

=UNIQUE(A1:A9)

In E1:

=MINIFS(B1:B9,A1:A9,D1#)

Format column E as a date.

Re: Earliest date

Hi @jlgomes

With a Pivot Table. Drop Dates to the Values area, aggregation = Min:

Re: Earliest date

Thanks @NikolinoDE. Allways returns 00-01-1900

Re: Earliest date

Thanks, allways returns 00-01-1900

Thanks

Re: Earliest date

array formula - got o the formula and press at the same time " Ctrl + Shift + Enter " to complete, then you will see the right date

Re: Earliest date

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));``````

Re: Earliest date

Are you sure you used the correct ranges?

Re: Earliest date

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

Re: Earliest date

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)
)``````

Re: Earliest date

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 ) )``````

Re: Earliest date

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!