Return a smallest date

[Freshman]

Dear experts,

I've rows of records which column A is for dates and column E is for
saleman's names:

1 Aug 2007 Tommy
15 Aug 2007 Tommy
26 Sep 2007 Tommy
4 July 2007 Peter
25 Sep 2007 Peter
18 Nov 2007 Peter

Is there a function which can filter out the earliest date for Tommy,
Peter....etc.
Please advise.

Thanks & regards.

 

[carlo]

If your Data is sorted, then you could look up the first value of
Tommy and
that would be the lowest date.

Sort your Data first after Column B, than Column A.

Then, in order that VLookup works, you need to have
your lookup field in the first column, so either change column
b and a or copy column a into column c which you can hide.

then put a vlookup formula in a cell you want.

hth

Carlo

 

[Max]

Think you could use something like this, array-entered*
=INDEX(A2:A100,MATCH(MIN(IF(E2:E100="Tommy",A2:A100)),IF(E2:E100="Tommy",A2:A100),0))

*Press CRTL+SHIFT+ENTER to confirm the formula

 

[T. Valko]

Then, in order that VLookup works, you need to have

your lookup field in the first column, so either change column
b and a or copy column a into column c which you can hide.

Or, without flipping the columns you could use:

=INDEX(A:A,MATCH("Tommy",B:B,0))

 

[Teethless mama]

=MIN(IF(B1:B6="Peter",A1:A6))

ctrl+shift+enter, not just enter

 

[yshridhar]

Cheers Mama. Your formula simplified my array extended
fro
=IF(ROWS($1:1)<=COUNTIF(RCPT!$B$2:$B$1500,$B$1),INDEX(RCPT!$D$2:$D$1500,SMALL(IF(RCPT!$B$2:$B$1500=$B$1,ROW(RCPT!$D$2:$D$1500)-MIN(ROW(RCPT!$D$2:$D$1500))+1),ROWS($1:1))),"")
t
=IF(ROWS($1:1)<=COUNTIF(RCPT!$B$2:$B$1500,$B$1),SMALL(IF(RCPT!$B$2:$B$1500=$B$1,RCPT!$D$2:$D$1500)),(ROW(1:1)),"")
Thanks alot
regards
Sridhar

 

[Freshman]

Dear Max,

Thanks for tips and it works well. Best regards.

 

[Freshman]

Hi Mama,

Thanks for your tips. Regards.

=MIN(IF(B1:B6="Peter",A1:A6))

ctrl+shift+enter, not just enter

 

[Max]

Welcome, Freshman.