Find the most recent date

[Guest]

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!

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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,

 

[Don Guillett]

You could make a list of the ID 's and place next to top one>ARRAY enter
it> and THEN copy down.
=MAX(($A$2:$A$8=a2)*($B$2:$B$8))

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk