Earliest date

Copper Contributor

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.

jlgomes_0-1660719042670.png

I´m struggling to get the unique name and the earliest date as shown above.

Any suggestion?

Thanks,

José

13 Replies

@jlgomes 

{=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.

 

NikolinoDE

I know I don't know anything (Socrates)

@jlgomes 

In D1:

=UNIQUE(A1:A9)

In E1:

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

Format column E as a date.

Hi @jlgomes 

 

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

_Screenshot.png

Thanks @NikolinoDE. Allways returns 00-01-1900 

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

@jlgomes 

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

 

@jlgomes 

Are you sure you used the correct ranges?

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

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

image.png

@Peter Bartholomew 

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

image.png

@Sergei Baklan 

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!