Formula help

[iluvsafc]

Hi all
I have a problem that I’m stuck with and I cannot for the life of me come up with a formula that works!

I have a data set that is duplicating on ID number as there is more than one expiry date per ID.

I’m after a formula that would look up each ID number in column A but bring back the most recent expiry date.

[TABLE="width: 610"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID Number
[/TD]
[TD]Expiry Date
[/TD]
[TD]Formula to bring back most recent expiry date per ID number????
[/TD]
[TD]Expected result
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]01
[/TD]
[TD]01/01/2014
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]01
[/TD]
[TD]01/01/2015
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]01
[/TD]
[TD]01/01/2016
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]02
[/TD]
[TD]01/01/2014
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]02
[/TD]
[TD]01/01/2015
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]02
[/TD]
[TD]01/01/2016
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]03
[/TD]
[TD]01/01/2014
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]03
[/TD]
[TD]01/01/2015
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]03
[/TD]
[TD]01/01/2016
[/TD]
[TD]
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
</tbody>[/TABLE]

My ideal result would be a separate pivot table (or something along the lines!) that would show the following –

[TABLE="width: 129"]
<tbody>[TR]
[TD]01
[/TD]
[TD]01/01/2014
[/TD]
[/TR]
[TR]
[TD]02
[/TD]
[TD]01/01/2015
[/TD]
[/TR]
[TR]
[TD]03
[/TD]
[TD]01/01/2016
[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated!
Cheers
Lee

 

[gaz_chops]

Try this, enter as array, Ctrl, Shift & Enter

=MAX(IF($B$2:$B$10=$B2,$C$2:$C$10))

 

[Special-K99]

in A11 put 1
in A12 put 2
in A13 put 3

in B11
=MAX(IF(B$1:B$9=A11,C$1:C$9,))
Array formula, use Ctrl-Shift-Enter

copy to B12 and B13