Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

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

enter image description here

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

enter image description here

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

enter image description here

share|improve this answer
    
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).

share|improve this answer
    
Beware that Remove Duplicates will delete the duplicate values, not just filter them. –  Excellll Mar 26 at 20:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.