| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the most recent date - Excel 2K
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........ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding the most recent date - Excel 2K
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 "tech1NJ" wrote in message ... 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........ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding the most recent date - Excel 2K
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(B20,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. -- Thanks, Shane Devenshire "tech1NJ" wrote: 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........ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding the most recent date - Excel 2K
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... -- tech1NJ "ShaneDevenshire" wrote: 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(B20,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. -- Thanks, Shane Devenshire "tech1NJ" wrote: 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........ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
| most recent date | Excel Discussion (Misc queries) | |||
| Looking up the most recent date | Excel Worksheet Functions | |||
| Finding most recent date by customer number | Excel Worksheet Functions | |||
| Finding the most recent month's (or whatever) data | Excel Discussion (Misc queries) | |||
Linear Mode
