Solved

Find Most Current Date From List

Posted on 2015-01-09
8
27 Views
Last Modified: 2016-03-20
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
0
Comment
Question by:tomfarrar
  • 4
  • 2
  • 2
8 Comments
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 200 total points
ID: 40540779
find attached.
EE.xlsx
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40540894
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40540897
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40540943
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40540948
tomfarrar

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

so i recommend you use it.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40541009
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
 
LVL 31

Accepted Solution

by:
Rob Henson earned 300 total points
ID: 41511735
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41511751
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A Short Story about the Best File Recovery Software – Acronis True Image 2017
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now