Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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)) -- tech1NJ "tech1NJ" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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. -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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 "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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: -- tech1NJ "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
Try this...
Array entered** : =MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D1:D6)),Sheet2!A 1: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 -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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: -- tech1NJ "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
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:D$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. -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try this... Array entered** : =MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D1:D6)),Sheet2!A 1: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 -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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: -- tech1NJ "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
Hi,
this seems to work although I havn't adjusted for your addresses MAX(ISNUMBER(FIND(A10,D$2:D$7))*(A$2:A$7+B$2:B$7)) A10 has the name you want to look up, D2:D7 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. -- Thanks, Shane Devenshire "tech1NJ" wrote: 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
Wooohoooo...
That works. Thanks a million... -- tech1NJ "T. Valko" wrote: Try this... Array entered** : =MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D1:D6)),Sheet2!A 1: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 -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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: -- tech1NJ "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting date and time with Excel 2K
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... Wooohoooo... That works. Thanks a million... -- tech1NJ "T. Valko" wrote: Try this... Array entered** : =MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D1:D6)),Sheet2!A 1: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 -- Biff Microsoft Excel MVP "tech1NJ" wrote in message ... 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: -- tech1NJ "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "tech1NJ" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting specific data from Date and time strings | Excel Discussion (Misc queries) | |||
extracting time only | Excel Worksheet Functions | |||
extracting a date from a comment field in an excel file | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Extracting Time from a cell that has both the date and the time | Excel Discussion (Misc queries) |