Solved

Find Most Current Date From List

Posted on 2015-01-09
8
35 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
[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
8 Comments
 
LVL 26

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
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
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
 
LVL 26

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 33

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 33

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Excel shared file syncing 8 79
Convert Excel Column Headers to Rows 8 107
Excel Automation of Autosum 23 127
Excel conditional formatting. 15 39
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

740 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