Return a smallest date
 

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.

Return a smallest date
 

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

On Dec 10, 10:49 am, Freshman
wrote:
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.

Return a smallest date
 

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

*Press CRTL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Freshman" wrote:
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.

Return a smallest date
 

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


--
Biff
Microsoft Excel MVP


"carlo" wrote in message
...
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

On Dec 10, 10:49 am, Freshman
wrote:
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.

Return a smallest date
 

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

ctrl+shift+enter, not just enter


"Freshman" wrote:

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.

Return a smallest date
 

Cheers Mama. Your formula simplified my array extended
from
=IF(ROWS($1:1)<=COUNTIF(RCPT!$B$2:$B$1500,$B$1),IN DEX(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))),"")
to
=IF(ROWS($1:1)<=COUNTIF(RCPT!$B$2:$B$1500,$B$1),SM ALL(IF(RCPT!$B$2:$B$1500=$B$1,RCPT!$D$2:$D$1500)), (ROW(1:1)),"")
Thanks alot
regards
Sridhar

"Teethless mama" wrote:

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

ctrl+shift+enter, not just enter


"Freshman" wrote:

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.

Return a smallest date
 

Dear Max,

Thanks for tips and it works well. Best regards.

"Max" wrote:

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

*Press CRTL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Freshman" wrote:
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.

Return a smallest date
 

Hi Mama,

Thanks for your tips. Regards.

"Teethless mama" wrote:

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

ctrl+shift+enter, not just enter


"Freshman" wrote:

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.

Return a smallest date
 

Welcome, Freshman.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Freshman" wrote in message
...
Dear Max,

Thanks for tips and it works well. Best regards.