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.

Mission 1....COMPLETED...................

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.

My 2nd mission with this same book....NEED ADDITIONAL HELP WITH THIS ONE....

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

Please Help........

 

[Ashish Mathur]

Hi,

In cell D2 of sheet 1, use the following formula =C2&COUNTIF($C$1:C2,C2) and
copy down. In cell B2 of sheet 2, enter the following formula and copy down

=INDEX(Sheet1!$A$1:$D$9,MATCH($A2&COUNTIF(Sheet1!$C$2:$C$9,Sheet2!$A2),Sheet1!$D$1:$D$9,0),1).
In cell C2 of sheet 2, enter the following formula and copy down

=INDEX(Sheet1!$A$1:$D$9,MATCH($A2&COUNTIF(Sheet1!$C$2:$C$9,Sheet2!$A2),Sheet1!$D$1:$D$9,0),2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[ShaneDevenshire]

Hi,

I'm going to assume that the formula given you for the count was something
like
=COUNTIF(Sheet1!C$2:C$23,Sheet2!A2)
In C2 of the Sheet2 Array enter the formula

=IF(B2>0,MAX((Sheet1!C$2:C$23=A2)*(Sheet1!A$2:A$23+Sheet1!B$2:B$23)),"Not
Found")

Note I'm working on row 2 you should adjust as necessary. Array enter means
you press Shift+Ctrl+Enter rather than Enter when you enter the fomula.

If this helps, please click the Yes button.

 

[tech1NJ]

Hello ShaneDevenshire,

The formula you provided does not appear to work. The possible reason for it
is that I have the following formula on Sheet2 B2.

=SUMPRODUCT(--ISNUMBER(SEARCH(A4,Sheet1!$C$2:$C$15012)))
Can you assist? Thanks...