Excel 2007 : Enter a value in a cell to signify the last occurence of the same value (see detail!)

I have a table in Excel as follows:

Event Part_Number Process Stock_Position
10598 ADF345 Picked
10599 ADF345 Assembled
10599 ADF345 Inspection
10600 ADF345 Packed
10600 EFT689 Picked
10601 EFT689 Assembled
10601 DBM165 Picked
10602 DBM165 Assembled
10602 WTP726 Picked
10603 WTP726 Assembled
10603 WTP726 Rejected


In each cell under stock position I want to write a formula to enter the process associated with the highest event code for each part number. For example for ADF345 'Packed' would appear under stock position.

Any ideas?

Thanks

Excellent!
Thanks for the quick reply.

I have now added an extra column to include a date, so now I want to write a formula to enter the process associated with the highest event code for each month and part number:

Event Part_Number Date Process Stock_Position
10598 ADF345 15/01/2012 Picked
10599 ADF345 29/01/2012 Assembled
10599 ADF345 15/02/2012 Inspection
10600 ADF345 29/02/2012 Packed Packed
10600 EFT689 15/02/2012 Picked
10601 EFT689 29/02/2012 Assembled Assembled
10601 DBM165 15/02/2012 Picked
10602 DBM165 29/02/2012 Assembled Assembled
10602 WTP726 29/01/2012 Picked
10603 WTP726 15/02/2012 Assembled
10603 WTP726 29/02/2012 Rejected Rejected

Therefore I would expect to see a stock position on event 10599 & 10603 also.

The formula I am using is: {=IF(OFFSET($D$1,MAX(IF(($B$2:$B$12=$B2),ROW($A$2:$A$12)-1,"")),0)=D2,D2,"")}

Thanks

Actually managed to solve it using the formula you suggested just slightly modified:

=IF(OFFSET($D$1,MAX(IF(($B$2:$B$12&MONTH($C$2:$C$12)=$B3&MONTH($C3)),ROW($A$2:$A$12)-1,"")),0)=D3,D3,"")

Thanks