Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the most recent date
Hey all,
Is there a way to find / fitler by the most RECENT date entry. I.E. I have the following data set: ID DATE 1 12/2/2006 1 12/7/2006 (this is the date I want displayed for THAT patient ID) 2 4/6/2007 (this is the date I want displayed for THAT patient ID) 2 7/7/2006 3 9/9/2007 3 8/7/2007 3 9/10/2007 (this is the date I want displayed for THAT patient ID) You see, each patient comes in on different dates. What I need to see and analyze are JUST those dates for each patient that were the most recent. Is there a way to perform a filter that does this? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the most recent date
With ID in Column A and Date in Column B try:
=MAX((A2:A8=2)*(B2:B8)) This is an array formula which must be entered with Ctrl + Shift + Enter -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Access Joe" wrote in message ... Hey all, Is there a way to find / fitler by the most RECENT date entry. I.E. I have the following data set: ID DATE 1 12/2/2006 1 12/7/2006 (this is the date I want displayed for THAT patient ID) 2 4/6/2007 (this is the date I want displayed for THAT patient ID) 2 7/7/2006 3 9/9/2007 3 8/7/2007 3 9/10/2007 (this is the date I want displayed for THAT patient ID) You see, each patient comes in on different dates. What I need to see and analyze are JUST those dates for each patient that were the most recent. Is there a way to perform a filter that does this? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the most recent date
You could make a list of the ID 's and place next to top oneARRAY enter
it and THEN copy down. =MAX(($A$2:$A$8=a2)*($B$2:$B$8)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Access Joe" wrote in message ... Hi Sandy, Thanks for the response. I'm not familiar with array formulas so I have two questions: a - where would this formula be entered (i.e. column C next to each date) b - what's the purpose of the CTRl / Shift / enter thing? Never seen that before and don't know what it means. Thanks, "Sandy Mann" wrote: With ID in Column A and Date in Column B try: =MAX((A2:A8=2)*(B2:B8)) This is an array formula which must be entered with Ctrl + Shift + Enter -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Access Joe" wrote in message ... Hey all, Is there a way to find / fitler by the most RECENT date entry. I.E. I have the following data set: ID DATE 1 12/2/2006 1 12/7/2006 (this is the date I want displayed for THAT patient ID) 2 4/6/2007 (this is the date I want displayed for THAT patient ID) 2 7/7/2006 3 9/9/2007 3 8/7/2007 3 9/10/2007 (this is the date I want displayed for THAT patient ID) You see, each patient comes in on different dates. What I need to see and analyze are JUST those dates for each patient that were the most recent. Is there a way to perform a filter that does this? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the most recent date
Hi Joe,
a - where would this formula be entered (i.e. column C next to each date) You can enter it anywhere you like including Don's excellent suggestion and it will and it will look at the lists of ID's and Dates. Another alternative, but still using Don's idea of using a cell reference instead of hard coding the ID # into the formula, is it have a cell - say D2 but it could be any cell - then use the formula (placed in any other cell): =MAX((A2:A8=D2)*(B2:B8)) Then whatever ID you put in D2 will be used to give you the latest date. If D2 is empty then you will get 0 returned. If you would rather have an empty looking cell then use the forumla: =IF(D2="","",MAX((A2:A8=D2)*(B2:B8))) b - what's the purpose of the CTRl / Shift / enter thing? It tells XL to look at all the elements in the array. Without it XL would look at only one cell. As you may have discovered *array entering* a formula puts curly braces around it as in: {=MAX((A2:A8=D2)*(B2:B8))} But don't put them in yourself - that will not work. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Access Joe" wrote in message ... Hi Sandy, Thanks for the response. I'm not familiar with array formulas so I have two questions: a - where would this formula be entered (i.e. column C next to each date) b - what's the purpose of the CTRl / Shift / enter thing? Never seen that before and don't know what it means. Thanks, "Sandy Mann" wrote: With ID in Column A and Date in Column B try: =MAX((A2:A8=2)*(B2:B8)) This is an array formula which must be entered with Ctrl + Shift + Enter -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Access Joe" wrote in message ... Hey all, Is there a way to find / fitler by the most RECENT date entry. I.E. I have the following data set: ID DATE 1 12/2/2006 1 12/7/2006 (this is the date I want displayed for THAT patient ID) 2 4/6/2007 (this is the date I want displayed for THAT patient ID) 2 7/7/2006 3 9/9/2007 3 8/7/2007 3 9/10/2007 (this is the date I want displayed for THAT patient ID) You see, each patient comes in on different dates. What I need to see and analyze are JUST those dates for each patient that were the most recent. Is there a way to perform a filter that does this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find the most recent date in a column based on other column | Excel Worksheet Functions | |||
date function to find most recent order | Excel Worksheet Functions | |||
Find most recent date | Excel Worksheet Functions | |||
Find most recent date in a row of dates | Excel Discussion (Misc queries) | |||
How do I find my recent postings if I can't remember title? | Excel Discussion (Misc queries) |