Find Most Current Date From List

Think this may need an array formula which I am not yet familiar with.  Need to get the most recent date from a list for the items in column A.  Sample included.  I'll take good notes for next time.  Thanks.
C--Users-E221037-Desktop-EE.xlsx
LVL 7
tomfarrarAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Take a look at the Dbase functions DMAX in particular. Syntax:

=DMAX(DataSet,Field,Criteria)

These are useful when you are trying to get one result as opposed to a result against each row. Unfortunately, the criteria bit has to be in a small table of its own.

I have done something similar in the past where I had to use VBA to do the calculation for each row and then populate the cell with the result. The dataset that I was working with was 800-900k rows and took 3+ hours to run. I had left it running overnight but had something in the script that noted start and finish times.

How about creating a pivot table? This will give you a list of unique references and can give a MAX date against each. If needed back in the original data then use a lookup on the pivot table for each reference.
0
 
ProfessorJimJamConnect With a Mentor Commented:
find attached.
EE.xlsx
0
 
tomfarrarAuthor Commented:
Sorry I was not clear in my haste.  I need to get the latest date for each item in Column A.  If you notice in the example I included rows 4 and 5 have the same item numbers in column A, but different dates in column B.  I need the function to identify 09/10/14 as the latest date.  

The file I provided is only a subset of the total list.  The list I am working with has 500,000 records.  Thanks.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
tomfarrarAuthor Commented:
Actually it was not just rows 4 and 5 with the same item numbers, but rows 4 through 9.  But the answer should still be 09/10/14.  

12621008885      09/10/14
12621008885      08/12/14
12621008885      03/18/14
12621008885      05/28/13
12621008885      09/10/14
12621008885      09/10/14
0
 
tomfarrarAuthor Commented:
Think I figured it out with some internet help.  If you have other ideas,

=MAX(IF(A2=$A$2:$A$41, $B$2:$B$41))
0
 
ProfessorJimJamCommented:
tomfarrar

your formula is correct,  i do not think there would be a better shorter one.

so i recommend you use it.
0
 
tomfarrarAuthor Commented:
Yea, that could be the case but with 500,000 rows it looks like it might take all day to do the calculation.  It has been 30 minutes and it is only 12% done.  Hmmmm...
0
 
Rob HensonFinance AnalystCommented:
See attached with Pivot, took matter of seconds to create and update. Might take longer with 500k rows.
Copy-of-C--Users-E221037-Desktop-EE.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.