Vlookup return most recent date (value)

Hello,

I have a set of data where Column A contains an personal ID# (001).  Column 
B contains a timestamp.  My table has multiple entries for each ID# and 
different timestamps for each entry.  I want to use a vlookup to find the 
MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.

Thanks,
Ryan
0
Utf
4/30/2010 1:48:01 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
1892 Views

Similar Articles

[PageSpeed] 46

With your data in ColA/B and the query ID in cell C1 try the below

=SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))

-- 
Jacob (MVP - Excel)


"ryanholliday" wrote:

> Hello,
> 
> I have a set of data where Column A contains an personal ID# (001).  Column 
> B contains a timestamp.  My table has multiple entries for each ID# and 
> different timestamps for each entry.  I want to use a vlookup to find the 
> MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
> 
> Thanks,
> Ryan
0
Utf
4/30/2010 1:55:01 PM
This is an ARRAY formula that must be entered/edited using ctrl+shift+enter

=MIN(IF(a6:a66="a",B6:B66))

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"ryanholliday" <ryanholliday@discussions.microsoft.com> wrote in message 
news:BC794D6A-583B-40F2-B833-C3F2CEDFEC42@microsoft.com...
> Hello,
>
> I have a set of data where Column A contains an personal ID# (001). 
> Column
> B contains a timestamp.  My table has multiple entries for each ID# and
> different timestamps for each entry.  I want to use a vlookup to find the
> MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
>
> Thanks,
> Ryan 

0
Don
4/30/2010 1:58:37 PM
Starting in D1 I have a column of unique IDs - made with Advanced Filter
In E1 I used this array formula =MAX(IF(A:A=D1,B:B))
(Array formula - so complete it with CTR+SHIFT+ENTER not just ENTER)
You will need to format the cell as Date otherwise it will display the 
5-digit serial number of the date
Copy down the column
If you use ranges, make use you use absolute references as in 
=MAX(IF($A$1:$A$100=D1,$B$100:$B1$100))
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"ryanholliday" <ryanholliday@discussions.microsoft.com> wrote in message 
news:BC794D6A-583B-40F2-B833-C3F2CEDFEC42@microsoft.com...
> Hello,
>
> I have a set of data where Column A contains an personal ID# (001). 
> Column
> B contains a timestamp.  My table has multiple entries for each ID# and
> different timestamps for each entry.  I want to use a vlookup to find the
> MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
>
> Thanks,
> Ryan 

0
Bernard
4/30/2010 2:03:37 PM
Don:
MIN or MAX if he wants the most recent?
Bernard

"Don Guillett" <dguillett1@gmail.com> wrote in message 
news:uNiI90G6KHA.3656@TK2MSFTNGP06.phx.gbl...
> This is an ARRAY formula that must be entered/edited using 
> ctrl+shift+enter
>
> =MIN(IF(a6:a66="a",B6:B66))
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "ryanholliday" <ryanholliday@discussions.microsoft.com> wrote in message 
> news:BC794D6A-583B-40F2-B833-C3F2CEDFEC42@microsoft.com...
>> Hello,
>>
>> I have a set of data where Column A contains an personal ID# (001). 
>> Column
>> B contains a timestamp.  My table has multiple entries for each ID# and
>> different timestamps for each entry.  I want to use a vlookup to find the
>> MOST RECENT timestamp for EACH ID.  Any help would be greatly 
>> appreciated.
>>
>> Thanks,
>> Ryan
> 
0
Bernard
4/30/2010 2:06:15 PM
I could not get that to work... In it's current form it just returned zero. I 
tried 

=SUMPRODUCT(MAX(--($A$2:$A$100=C1), $B$2:$B$100))

But that just returned the max date regardless of the ID selected. Here is 
what I ended up with...

=MAX(IF($A$2:$A$100=C1, $B$2:$B$100))

***Note this is an array formula and MUST be committed using 
Shift + Ctrl + <Enter>

-- 
HTH...

Jim Thomlinson


"Jacob Skaria" wrote:

> With your data in ColA/B and the query ID in cell C1 try the below
> 
> =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "ryanholliday" wrote:
> 
> > Hello,
> > 
> > I have a set of data where Column A contains an personal ID# (001).  Column 
> > B contains a timestamp.  My table has multiple entries for each ID# and 
> > different timestamps for each entry.  I want to use a vlookup to find the 
> > MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
> > 
> > Thanks,
> > Ryan
0
Utf
4/30/2010 2:34:02 PM
Jim, could you please try again..with the alternate query IDs in cell c1...

Col A	Col B	Col C
1	7:21 PM	3
1	7:22 PM	
2	8:05 PM	
2	8:06 PM	
3	9:06 PM	
3	9:07 PM	


-- 
Jacob (MVP - Excel)


"Jim Thomlinson" wrote:

> I could not get that to work... In it's current form it just returned zero. I 
> tried 
> 
> =SUMPRODUCT(MAX(--($A$2:$A$100=C1), $B$2:$B$100))
> 
> But that just returned the max date regardless of the ID selected. Here is 
> what I ended up with...
> 
> =MAX(IF($A$2:$A$100=C1, $B$2:$B$100))
> 
> ***Note this is an array formula and MUST be committed using 
> Shift + Ctrl + <Enter>
> 
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "Jacob Skaria" wrote:
> 
> > With your data in ColA/B and the query ID in cell C1 try the below
> > 
> > =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))
> > 
> > -- 
> > Jacob (MVP - Excel)
> > 
> > 
> > "ryanholliday" wrote:
> > 
> > > Hello,
> > > 
> > > I have a set of data where Column A contains an personal ID# (001).  Column 
> > > B contains a timestamp.  My table has multiple entries for each ID# and 
> > > different timestamps for each entry.  I want to use a vlookup to find the 
> > > MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
> > > 
> > > Thanks,
> > > Ryan
0
Utf
4/30/2010 2:40:01 PM
You are correct. I had an issue with Text vs Numbers...
-- 
HTH...

Jim Thomlinson


"Jacob Skaria" wrote:

> Jim, could you please try again..with the alternate query IDs in cell c1...
> 
> Col A	Col B	Col C
> 1	7:21 PM	3
> 1	7:22 PM	
> 2	8:05 PM	
> 2	8:06 PM	
> 3	9:06 PM	
> 3	9:07 PM	
> 
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Jim Thomlinson" wrote:
> 
> > I could not get that to work... In it's current form it just returned zero. I 
> > tried 
> > 
> > =SUMPRODUCT(MAX(--($A$2:$A$100=C1), $B$2:$B$100))
> > 
> > But that just returned the max date regardless of the ID selected. Here is 
> > what I ended up with...
> > 
> > =MAX(IF($A$2:$A$100=C1, $B$2:$B$100))
> > 
> > ***Note this is an array formula and MUST be committed using 
> > Shift + Ctrl + <Enter>
> > 
> > -- 
> > HTH...
> > 
> > Jim Thomlinson
> > 
> > 
> > "Jacob Skaria" wrote:
> > 
> > > With your data in ColA/B and the query ID in cell C1 try the below
> > > 
> > > =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))
> > > 
> > > -- 
> > > Jacob (MVP - Excel)
> > > 
> > > 
> > > "ryanholliday" wrote:
> > > 
> > > > Hello,
> > > > 
> > > > I have a set of data where Column A contains an personal ID# (001).  Column 
> > > > B contains a timestamp.  My table has multiple entries for each ID# and 
> > > > different timestamps for each entry.  I want to use a vlookup to find the 
> > > > MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
> > > > 
> > > > Thanks,
> > > > Ryan
0
Utf
4/30/2010 3:04:01 PM
OK. I remember a similar response from you few months back while I used 
SUMPRODUCT() MAX() combination; but I found that too late to respond...It 
works when the criteria (here ColA) is both text and numerics...Thanks Jim

-- 
Jacob (MVP - Excel)


"Jim Thomlinson" wrote:

> You are correct. I had an issue with Text vs Numbers...
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "Jacob Skaria" wrote:
> 
> > Jim, could you please try again..with the alternate query IDs in cell c1...
> > 
> > Col A	Col B	Col C
> > 1	7:21 PM	3
> > 1	7:22 PM	
> > 2	8:05 PM	
> > 2	8:06 PM	
> > 3	9:06 PM	
> > 3	9:07 PM	
> > 
> > 
> > -- 
> > Jacob (MVP - Excel)
> > 
> > 
> > "Jim Thomlinson" wrote:
> > 
> > > I could not get that to work... In it's current form it just returned zero. I 
> > > tried 
> > > 
> > > =SUMPRODUCT(MAX(--($A$2:$A$100=C1), $B$2:$B$100))
> > > 
> > > But that just returned the max date regardless of the ID selected. Here is 
> > > what I ended up with...
> > > 
> > > =MAX(IF($A$2:$A$100=C1, $B$2:$B$100))
> > > 
> > > ***Note this is an array formula and MUST be committed using 
> > > Shift + Ctrl + <Enter>
> > > 
> > > -- 
> > > HTH...
> > > 
> > > Jim Thomlinson
> > > 
> > > 
> > > "Jacob Skaria" wrote:
> > > 
> > > > With your data in ColA/B and the query ID in cell C1 try the below
> > > > 
> > > > =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))
> > > > 
> > > > -- 
> > > > Jacob (MVP - Excel)
> > > > 
> > > > 
> > > > "ryanholliday" wrote:
> > > > 
> > > > > Hello,
> > > > > 
> > > > > I have a set of data where Column A contains an personal ID# (001).  Column 
> > > > > B contains a timestamp.  My table has multiple entries for each ID# and 
> > > > > different timestamps for each entry.  I want to use a vlookup to find the 
> > > > > MOST RECENT timestamp for EACH ID.  Any help would be greatly appreciated.
> > > > > 
> > > > > Thanks,
> > > > > Ryan
0
Utf
4/30/2010 3:09:02 PM
Reply:

Similar Artilces:

Earned Value Report in Project 2007
I'm using MS Project 2007, have input all my tasks, costs, set baseline so all my numbers are populating great. I'm using the visual report to generate the earned value over time report (the pivot table and chart). Unfortunately while I am really good at most things Excel, pivot tables is not one of them. Project is populating the table with Quarters 1, 2 and 3. I only need Quarter 1 for my report. Is there a way to hide or delete the other two quarters from the table? Thanks for the help! Teri Hi Teri, I am guessing that you have your "Time Weekly Calendar&q...

Can I exclude the criteria Value from the query results?
I am using the [Dupe_Joiner_Title_ID] to match a list of records from a duplicates table against an ID form field, but want to exclude the actual value that the query uses as the criteria from being seen in the continous form that is linked to the query, but shows all other matching records for the form's current record. This is the criteria code: [Forms]![frm_Runs]![frm_Street_Joiner_Main].[Form]![frm_Street_Joiner_Sub].[Form]![Joiner_Title_ID] that is in the 'Dupe_Joiner_Title_ID: Joiner_Title_ID' column. This is my SQL: SELECT Tbl_Street_Joiner_Dupes.Address, Tbl_Stree...

Using SUMIFS with date range
I am trying to sum a column of cells if several criteria are met...1. equal to specific customer name, 2. is greater than or equal to a beginning date entered into a specific cell and 3. is less than or equal to an end date entered into another specific cell. I can get the formula to work if I use the beginning and end dates in the SUMIFS formula, but I don't want to change the formula each time I run the spread. So I want to be able to data enter the begin and end dates in certain cells (line in B1 and B2) so I only change those dates to recalculate the totals. Somethin...

Changing line appearance for future values
Using Excel 2007. Is it possible to change the appearance of a line in a chart for values in the future (like projections)? I would like to go from solid to dashed and keep the same color and weight. Either you format line pieces individually (manual work). Either you use 2 series to plot in the chart. Use IF functions to see on which series a given value should be shown. Use NA() for values that should not be plotted. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "RhysPieces" wrote: > Using Excel 2007. Is it possible to change the appearance of a line...

Conditional formating based on another cell value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've managed to use the solution described below, but I haven't been able to figure out how to use this for formating a cell based on a date entered into another cell. <br><br>Example: When 1/15/10 is entered into AG4, P4 is highlighted green. <br><br>Any help would be appreciated. <br> ----- <br> &quot;On 6/30/08 7:54 PM, in article 59b52d02.-1@webcrossing.caR9absDaxw, <br> &quot;Simon@officeformac.com&quot; wrote:http://www.officeformac.com/macimages/btn_publish.gif &l...

date format 01-02-10
How can I set a date format for any cell and user has to use that format otherwise user is not allowed to enter any date. Hi you can only do it with code. The following is some event code which would ensure that only dates can be entered in column A, strating from row 2. Adjust to suit your nededs. also, date format has been set as dd mmm yyyy e.g. 02 Jan 2010, again change to suit the format you want. Private Sub Worksheet_Change(ByVal Target As Range) Dim edate As Date If Target.Row < 2 Then Exit Sub ' change row number from 2 if required If Target.Co...

Function to format a value into percent with no decimal
I have about 50 rows of data, each with 2 columns. The first column (A) holds a number; the format is General. The second column (B) divides the number in A over 51, using this formula =A1/51 (and =A2/51, =A3/51, etc.). The format for column B is Percentage with 0 decimal places, so the results look like 25%, 4%, etc. I'd like to create a third column (C) which concatenates the results of columns A & B to yield something like: 13 (25%) But instead I get: 13 (0.254901960784314) The function I'm using is: =B1 & " (" & C1 & ")" I'm thinking I...

Date "yyyymm"
I have a worksheet were the dates are displayed as 200901 (yyyymm --- no slash) in column A. I need to add a formula to cells in colum C that will only substract any given number of months from this date, such as 200901 minus 13 months will equal 200811. Now, on the same boat, I would like to show the new date displayed as 11/01/2008. Thank you On Tue, 15 Dec 2009 06:31:02 -0800, Memphis <memphisland@bogusaddress.com> wrote: >I have a worksheet were the dates are displayed as 200901 (yyyymm --- no >slash) in column A. >I need to add a formula to cells in c...

Pay Period From/To dates on Payroll check voids
When a payroll transaction is entered or a build is processed, pay period from and to dates are entered. These dates are stored as 'trxbegdt' and' trxenddt' on UPR30300. However, when a check is voided, the 'trxbegdt' and trxenddt' on the UPR30300 table holds the actual check void date and not the original pay period from and to dates. SmartList Builder, Crystal Reports and Excel data query tools can be used to pull data based on the pay period date ranges for performance based reporting, but voids cause inconsistencies in the analysis since the dates are ...

DIsplaying negative time values
Hi, I have two columns of time values. I need to determine the difference between the two values and return either a positive or negative time value. The table below is in hours and minutes Time A Time B 09:00:00 09:00:00 09:00:00 09:03:00 09:00:00 08:58:00 In the above example First row should be "00:00:00", Second row should be "00:03:00", and Third row should be "-00:02:00" When subtracting Time B from Time A for the first two rows i get the correct value, but the third row returns as "##########...

Styles and Set Numbering Value problem
Running MS Office Pro 2007 with Windows XP Pro. I have styles defined for numbered lists. These styles have simple characteristics, like indent, space before, etc. - nothing special. I want the numbered lists to appear throughout the document, with each starting at the number 1. When I try to start a new list using the Set Numbering Value dialog, I get a message saying "Changing the number format for this list updates the style "NumList" and reapplies the style to each paragraph. Do you want to continue?" If I select no, the starting number is not chan...

Date query 01-21-10
I have a table which contains a field called "DUEDATE", data type is DATE/TIME, formatted as LONGDATE. I use it to enter in dates when projects are due. How can I write a query which returns "DUEDATE" items for the current date. Example: Today is January 21, 2010 and I want to retrieve everything from the table with "DUEDATE" of january 21,2010. If you only store the date part and only want to return records where the DueDate is the current date, set the criteria under DueDate to: =Date() Duane Hookom MS Access MVP "Iago" <Iago@d...

Returning last number(time) in a row of cells
Need some help with this one please; Row A4 thru J4 A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 12:00 15:00 16:00 I need to return to cell K4 the last number in the above row. I do not know what formula to use. Any help would be greatly appreciated. There are several ways to achieve that, - here are two: http://img9.imageshack.us/img9/9398/nonamegi.png Micky "v1rt8" wrote: > Need some help with this one please; > Row A4 thru J4 > A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 > 12:00 15:00 16:00 > ...

How to suppress zero value check checks and remittance
Any one knows how to avoid printing the zero value remitances during the checks run in PM. GP7.5 Thanks in Advance. ...

vlookup help #2
hi i am providing you with my problem with attached file. i will be very grateful to you if you can solve my probem Attachment filename: help me.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46482 -- Message posted from http://www.ExcelForum.com Hi first: you should try to explain your problem in plain text. Most people won't open attachments :-) To your problems: Just enter the 4th parameter of the VLOOKUP function to look for exact matches. So change =VLOOKUP(A17,A25:C27,3) to =VLOOKUP(A17,A25:C27,3,0) and =VLOOKUP(A17,A25:C2...

Trying to select a specific range based on the time value of user form input
Here is the cmdOK_Click event of my user form. What I need it to do is based on the time input in the pckApptTime on my form to have it offset to a different range of cells (ie: if time is 7:15, then the active cell offset is range A1, looping for a blank cell. If the time is 8:30 am then the range begins at a10, 9:45 range a20, 1:15 range a30, 2:30 range a40, and 3:45 range a50) Where in the Sub do I put this information? and what do I put to have the OK_Click do this automatically? Private Sub cmdOK_Click() Range("A1").Select ActiveWorkbook.Save (this is to update the ...

Task with overdue dates print in smaller fonts?
In Outlook 2003, if I have a task with an overdue due date, it appears on screen just like all the other Tasks ... but when I print out my Tasks, it shows up in a smaller font. If I delete the due date, it prints in the same size font as everything else. Where do I set my preference for all tasks to be printed the same regardless of whether they have due dates or not? ...

Return cell characters after space
I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'. As there are many other examples, I need a function that will seek out the last ' ' in the cell content and then return all characters to the right hand-side of this ' '. e.g. 'Mr K Peters' would return 'Peters' Please let me know if I can clarify! Hi Andy =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) -- Jacob (MVP - Excel) "Andy" wrote: > I have a cell with 'Mr J. Smith' and want to return in an...

Maximum value
Hello, I have a table in the following format: ID Diagnosis Fraction 1 X48 0.01 1 Y52 0.5 2 U78 0.85 etc What I need is only those records that have the highest fraction for each ID. ID Diagnosis Fraction 1 Y52 0.5 2 U78 0.85 etc I have used the Max function in my query. However, that does not work because the information is also grouped by diagnosis as well as ID and this causes all of the lines to appear. Does anyone know how to get around this problem? I can't just bring through Fraction ...

Transferring option group value
I'm trying to create a form for adding new record only. How to do that ?. In that from,I want to put an option button ( group ) to shown or hide some text box ( control ) in my forms.But I also want to transferr the option group value to a field once the form close or the Save_cmd is clicked. Then in another qury I need to join the value transferred from the option group with another table so that the data appear as a text in another table. Please advice how to do that since I have no idea with coding? shiro wrote: >I'm trying to create a form for adding new record only. >...

Copy formulas, format, validation to sheet with values
Environment: Excel 2000, Win 2000 I have a workbook with 12 sheets, one for every month of a year, all the same size, same format, just different values. I am still making changes to the workbook application, e.g. formulas, formats, validation, conditional formatting. So I change the sheet for one month, and then have to get the changes duplicated for all the other 11 sheets in the workbook. What is the best way to do that, especially for the formulas? Paste Special with deleting the constants afterwards is not a solution here, as some of the target sheets already have values entered manually...

FREQUENCY and dates
Hi, I'm doing some performance analysis and have a large number of timestamp (formatted as dd/mm/yyyy hh:mm:ss) / transaction duration pairs in a spreadsheet. I'm trying to create a histogram showing number of transactions each hour, but when I use the FREQUENCY function I get very small numbers associated with each timestamp "bin". Am I missing something fundamental about the way Excel handles dates? After that I need a similar plot of average duration each hour or 30 minutes. I don't see any way to force FREQUENCY to do that. Related to this, I tried selecting the...

Why is date repeated in brackets in Outlook 2007 Calendar?
in day, week, month, views "Buddie" <Buddie@discussions.microsoft.com> wrote in message news:DF1E0322-20E0-4137-9708-5638D1F472D8@microsoft.com... > in day, week, month, views Do you have alternate calendars enabled? Tools>Options>Calendar Options is where you find that setting. Otherwise, check the Date format(s) in the Windows Time/Date settings. -- Brian Tillman [MVP-Outlook] Do you have a second calendar index enabled? look in tools, options, calendar options. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.ne...

sum a cell value to a range
Hi I need to sum value in A4 to a range B4:B12 every time I enter a new value in A4. How can I do it? Thanks in advance Osmario "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: > I need to sum value in A4 to a range B4:B12 every time I > enter a new value in A4. How can I do it? =SUM(A4,B4:B12) I wrote: > "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: >> I need to sum value in A4 to a range B4:B12 every time I >> enter a new value in A4. How can I do it? > > =SUM(A4,B4:B12) Or did you mean that you want to a...

Open file with variable date name based on current date
Hello, I have a folder containing different .xls report files with the name format "YYYY MM text.xls", where YYYY stands for year, MM for month. I need a macro which, when run from an excel file saved in a different loacation, based on the current date, e.g. January 7, 2009, will open the file "2009 01 text.xls". Thanks for your help, Ciperian Workbooks.Open "C:\Foldername\" & Format(Now(),"yyyy mm") & " text.xls" Except it will open 2010 01 text.xls ;-) HTH, Bernie MS Excel MVP "Ciprian" <C...