Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
formula to look up and return smallest date from a range of dates | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Return the smallest value | Excel Worksheet Functions | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |