Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Find Most Current Date From List

Posted on 2015-01-09
Medium Priority
40 Views
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
Question by:tomfarrar
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 2
• 2

LVL 27

Assisted Solution

ProfessorJimJam earned 800 total points
ID: 40540779
find attached.
EE.xlsx
0

LVL 7

Author Comment

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

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

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

LVL 27

Expert Comment

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

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 33

Accepted Solution

Rob Henson earned 1200 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 33

Expert Comment

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micrâ€¦
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn various types of data validation for different data types in Excel 2013.
###### Suggested Courses
Course of the Month10 days, 10 hours left to enroll

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

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