Identifying Duplicates and then finding most recent

[TaulPaul]

I need a little help.

I have a long list of serial numbers, and then a date associated with an activity for each serial number.

I am trying to identify duplicate entries for each serial number (i.e. I have performed some activity on some unique serial number multiple times), and then for those entries with duplicates find the most RECENT date of activity.

Here's the basic structure:
S/N Date
001 1/1/05
002 1/5/05
002 3/2/05
003 1/3/05
004 4/7/05
004 2/1/05

The desired output would be a list of ALL unique serial numbers, with the corresponding date of the most recent activity for each serial number, such as the following:

S/N Date
001 1/1/05
002 3/2/05
003 1/3/05
004 4/7/05

Thanks!

 

[Joe Was]

Use the AutoFilter off the Excel Toolbar.

 

[Aladin Akyurek]

"[M]ost recent" is not necessarily unambiguous, hence:

Book2
	A	B	C	D	E	F
1	S/N	Date		S/N	Date(1)	Date(2)
2	001	1/1/2005		001	1/1/2005	1/1/2005
3	002	1/5/2005		002	3/2/2005	3/2/2005
4	002	3/2/2005		003	1/3/2005	1/3/2005
5	003	1/3/2005		004	4/7/2005	2/1/2005
6	004	4/7/2005
7	004	2/1/2005
8
Sheet1

E2:

=MAX(IF($A$2:$A$7=D2,$B$2:$B$7))

which is confirmed with control+shift+enter (not just with enter) then copied down.

F2, copied down:

=LOOKUP(2,1/($A$2:$A$7=D2),$B$2:$B$7)

 

[TaulPaul]

Worked like a charm, Aladin, thank you!