Finding the most recent date -- Excel 2K

[tech1NJ]

I am working with a book and 2 sheets and have 2 Missions to accomplish.
Mission 1 is completed. Thanks to John C fast response. I need additional
help with Mission2 but you will need to know / read Mission 1 to understand
Mission 2.

Sheet 1 has data on 3 columns (Date on column A, Time on column B, text in
Column C) and 100s of rows. Sheet 2 has specific names in ColumnA3 to A18
that may show up on Sheet1 Column C. What I like to do is count the number of
times the names in Sheet2 appear in Sheet 1 Column C. The number of times
should be logged into Sheet2 B3 to B18. Again, this has been solved by John C.

I have one more function I need your help with. The same sheets 1 and 2 are
in play for this application. This time I added Columns C (Date) and Time
(Time) to Sheet 2. If the name in Sheet2 A3 was found in Sheet1 C3, I like to
know when the last date and time the person appears in Sheet1 C3 and log this
Date and Time into Sheet2 C3 (Date) and Sheet2 D3 (Time). If not found, then
Sheet 2 C3 should indicate "Not Found". Sheet1 Column A has the dates
(formated DD/MM/YY) and column B has the time (formated hh:mm AM). Your help
is greatly appreciated......

 

[ShaneDevenshire]

Hi,

I have set up the following example, but not between sheets, you will need
to make the adjustment to the ranges and sheets.

Suppose my data looks like this where the first name is in B2 and Dates are
in C and Times in D

Date Time
Shane 11/18/2008 4:21 PM
Shane 10/28/2008 12:41 PM
Sharon 11/12/2008 12:35 PM
Shane 11/1/2008 1:49 PM
Shane 11/25/2008 5:41 PM

This formula return the latest date for Shane
=MAX(IF((A3=$B$2:$B$6)*($C$2:$C$6&$D$2:$D$6),$C$2:$C$6))

Where the name is in cell A3. This formula is array entered (Press
Shift+Ctrl+Enter instead of Enter. I put the above formula in cell J3 for my
example

For the latest time on that date use

=MAX(IF((A3=$B$2:$B$6)*(J3=$C$2:$C$6),$D$2:$D$6))

Again this is array entered and J3 contains the previous formula.


If this helps, please click Yes.