How can I filter out all but the latest entry in Excel?

June 9, 2009 5:30 PM

MS Excel Problem: How can I filter out all but the latest entry for each unique ID?

I have a data set that consists of a lot ID number, an event date and then details surrounding it. The problem is that each lot ID will have a number of events (and thus a number of rows with the same ID number) and I only want the latest event. I've been playing with the 'advanced filter' option but can't think of a set of criteria to do this. The only solution thus far is to order them by ID and then date and do it manually, however this is going to take a lot of time.

Any suggestions?

posted by Serial Killer Slumber Party to computers & internet (4 comments total)

Have you tried MAX? you can use the Max(C:C) (where C is the row containing dates) to get the highest date and then use vlookup on the value that MAX returns to get the information relating to that date.
posted by amethysts at 5:42 PM on June 9

My suggestion: First, order by lot ID, then by date. Assuming that lot ID is in column A and date is in column B, put the following formula in, say cell C1: IF(A1=A2, 0, 1). Copy that formula all the way down column C. Filter on column C.
posted by mhum at 6:01 PM on June 9 [1 favorite]

I might not be following correctly (as I don't use vlookup a whole lot) but that seems like it would only help in finding information for a particular lot. I'm actually trying to cull any outdated event in order to intergrate the data in to ArcGIS so I have to remove the unused entries.

Is there a way I can use vlookup to quickly copy/paste only those selected by MAX? I've got about 80 files each with about 5000 unique IDs you see.
posted by Serial Killer Slumber Party at 6:02 PM on June 9

mhum, your idea works perfectly and is very simple too! I feel a bit daft for not coming up with it myself. Much appreciated!
posted by Serial Killer Slumber Party at 6:07 PM on June 9