Excel 2007 : MIN Date value return

[hotdog50]

Hi guys,

I have two columns as per below:

Bernard 24/01/2010
Bernard 27/02/2010
Peter 28/01/2009
Peter 28/05/2009

How can I add a third column to return the lowest value date for column a?

My list of names runs into thousands and the dates are contract dates, I need to show each persons first date where ever their name comes up.

Any help would be lovely.

[teylyn]

If column A is sorted ascending, you can use in C1 and copied down

=MIN(INDEX(B:B,MATCH(A1,A:A,0)):INDEX(B:B,MATCH(A1,A:A,1)))

The same minimum date will show for every user name.

If you sort by names first, and then by date, you can do a simple

=VLOOKUP(A1,A:B,2,FALSE)

[DonkeyOte]

Either way I would suggest you add a header row at which point:

If sorted by name but not by date then:

C2: =IF(A2=A1,C1,MIN(B2:INDEX(B:B,MATCH(A2,A:A,1))))
copied down

this way you only conduct the MIN calc once per name and avoid the exact match calc. altogether

If sorted by name and date then simply:

C2:
=IF(A2=A1,C1,B2)
copied down

(thereby minimising precedents)