two columns, one values, one dates. Need most current date for that value.

Hi,

There must be an easier way than me going through line by line of 12k lines to determine the most recent date. I really hope someone can help me.

Example of data below.

9699741 28/10/2013
9704789 06/02/2014
9704789 13/02/2014
9704789 20/02/2014
9844987 31/01/2014
9844987 07/02/2014
9844987 14/02/2014
9845011 14/02/2014
9845051 25/10/2013
9845225 19/02/2014
9845411 30/10/2013
9845439 03/02/2014
9845439 17/02/2014
9845442 07/02/2014
9845442 14/02/2014
9845446 07/02/2014
9845451 03/01/2014
9845451 24/01/2014

In the third column I need to determine the most recent date for the value in column A, example 9845451 would return 24/01/2014 as the result.

A million thank you's in advance..

You can use this array* formula in C1:

=MAX(IF(A$1:A$12000=A1,B$1:B$12000))

Format the cell as a date, then copy down.

*An array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

Hi GINSTER

As you have Excel 2010 you will have no need for array formulas!

See the attached workbook with two formula approaches and a pivot table option.