Need help extracting date and time with Excel 2K

[tech1NJ]

I have 2 sheets setup. Sheet 1 has name listed in column A and Sheet 2 has
data as listed in the example below. I need help with extracting the latest
date and time from sheet2 based on the name listed on sheet1. The problem
that I have is that Sheet2 Column D has more text than just the name. In this
example I like to retrive the latest date and time for rdupree and place it
into Sheet1 Column C in the same row as rdupree is. I currently am using the
following formula but it only works if Sheet2 Column D has only just the
name. It does not work if the extra text is in the cell. One more thing. I am
not allowed to remove the extra text from Column D in Sheet2.

Example1 - names listed in Sheet1 Column A
rdupree
jose
debby
pcaruso

Example2 - type of data on Sheet 2
Column A ColumnC ColumnD
1/8/2006 - 2:29 PM rdupree Authorized Testwireless or Upgrade
1/10/2006 - 5:19 AM rdupree Authorized Centron 48 input Node
1/11/2006 - 10:55 AM pcaruso Authorized Centron 128 input Node
1/11/2006 - 11:39 AM jose Authorized Testwireless or Upgrade
1/11/2006 - 11:44 AM debby Authorized Software Opt:
1/11/2006 - 11:47 AM debby Authorized Software Opt:

 

[tech1NJ]

Oops the formula that I am using is
=MAX(IF((A3=$B$2:$B$6)*($C$2:$C$6&$D$2:$D$6),$C$2:$C$6))

 

[Rick Rothstein]

Is the date and time on Sheet2 both in Column A or have you merged Columns A
and B somehow? If it is in Column A, is it a real Excel date which has been
formatted to look like you showed (the dash is non-standard)? It is a little
hard to tell... I'm assuming the names on Sheet2 are in Column C, right?
Does your data start on Row 1 on both sheets or do you have a header on Row
1 and the data starts on Row 2?

 

[T. Valko]

Can't tell from your example table what data is in what column.

Are the dates/times true Excel dates/times? They don't look it in your
example.

 

[tech1NJ]

The dates are in column A Sheet 2 and the times are in column C Sheet 2 and
the name are in Column D Sheet2. The dates and times are setup as excel dates
and times. Column B has the - (dash) and I am not using this column at this
time. The data on Sheet 2 start on row 2 and the names on Sheet 1 Column A
start on row 4.

 

[tech1NJ]

Here a better example of the table in Sheet 2
ColumnA ColumnB Column C ColumnD
1/8/2006 - 2:29 PM rdupree Authorized Testwireless or Upgrade
1/10/2006 - 5:19 AM rdupree Authorized Centron 48 input Node
1/11/2006 - 10:55 AM pcaruso Authorized Centron 128 input Node
1/11/2006 - 11:39 AM jose Authorized Testwireless or Upgrade
1/11/2006 - 11:44 AM debby Authorized Software Opt:
1/11/2006 - 11:47 AM debby Authorized Software Opt:

 

[tech1NJ]

Here is a better example of the table...

ColumnA ColumnB Column C ColumnD
1/8/2006 - 2:29 PM rdupree Authorized Testwireless or Upgrade
1/10/2006 - 5:19 AM rdupree Authorized Centron 48 input Node
1/11/2006 - 10:55 AM pcaruso Authorized Centron 128 input Node
1/11/2006 - 11:39 AM jose Authorized Testwireless or Upgrade
1/11/2006 - 11:44 AM debby Authorized Software Opt:
1/11/2006 - 11:47 AM debby Authorized Software Opt:

 

[T. Valko]

Try this...

Array entered** :

=MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D16)),Sheet2!A1:A6+Sheet2!C1:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Format as m/d/yyyy h:mm AM/PM

 

[Rick Rothstein]

Since the formula will be copied down, I think you need to make some of the
references absolute...

=MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D$1$6)),Sheet2!A$1:A$6+Sheet2!C$1:C$6))

To the OP... this is still array-entered**

**Commit using Ctrl+Shift+Enter, not just Enter by itself.

 

[ShaneDevenshire]

Hi,

this seems to work although I havn't adjusted for your addresses

MAX(ISNUMBER(FIND(A10,D$2$7))*(A$2:A$7+B$2:B$7))

A10 has the name you want to look up, D27 contains the messy text, A2:A7
the data and B2:B7 the time.

This formula is array entered which means you press Shift+Ctrl+Enter to
enter it rather than just pressing Enter.

 

[tech1NJ]

Wooohoooo...

That works. Thanks a million...

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Wooohoooo...

That works. Thanks a million...