# How can I filter for unique values using the most recent Date in a specific column?

I have 10K rows of data, some with multiple rows per employee, I need to filter by unique values by using the employee ID# in 1st column, but filter using the most recent date in another column. I am using Excel 2010. Is there a formula for this?

Example:

``````ID         NAME      ACT DATE         SALARY
123     JOE SMITH    01/02/2012      20000
123     JOE SMITH    02/01/2013      20000
``````

I would only want to see the 2nd row of data for this employee because the act date is the most recent.

EDIT In other words, I want to be able to filter the results of a previous filter.

-
This looks like the same problem as Vlookup and get the min value (date) to me, which has an accepted answer using formulas. – Reinier Torenbeek Mar 6 '13 at 6:11

If you turn this into a table, you can enable filters. This is then automatic for you.

Then click on the arrow in the header row of your new table and you'll see something like

If this doesn't work, just click on the Sort beside or the Filter icon under Data and Add Level.

-
I am trying to use a table, but how is it automatic? I do not see a filter for the date field for last or max date to advcd filter on? – Kelly Mar 5 '13 at 15:20
Select the entire table, then click on Data in the ribbon, Filter and then Add Level – Dave Mar 5 '13 at 15:29
That allows me to sort by 1st employee ID#, then by the newest date, but then how do I filter by the unique newest date field? – Kelly Mar 5 '13 at 15:31
@user204363 So you want to filter based upon the results of a previous filter? – Dave Mar 5 '13 at 15:32
Yes, exactly! What's the best way to accomplish this? Can Access do a calculation or is Excel capable? – Kelly Mar 5 '13 at 15:35

Select your entire sheet. Use the Sort function and choose the date column you want. Then order it from newest to oldest, press OK. Keep the entire sheet selected, now to to Remove Duplicates, unselect all columns and then choose the column you need unique values from. This method will put all newest dates at the top when it removes the duplicates, you're left with unique values that match to the top most (recent dates).

-
Beware that Remove Duplicates will delete the duplicate values, not just filter them. – Excellll Mar 26 '14 at 20:55