how to return the latest result...

[mycarpark]

if i have the following table...i want to return the latest results.
is it possible?

e.g.
ID Action Date
a1 jump 2/9/2008
a2 walk 3/9/2008
j10 jump 3/9/2008
a1 move 3/9/2008
a2 climb 5/9/2008


I want to have follow results...

ID Action Date Latest_action Latest_Date
a1 jump 2/9/2008 move 3/9/2008
a2 walk 3/9/2008 climb 5/9/2008
j10 jump 3/9/2008 jump 3/9/2008
a1 move 3/9/2008 move 3/9/2008
a2 climb 5/9/2008 climb
5/9/2008

 

[muddan madhu]

Try this !

Col A - Id
Col B - Action
Colc C - Date
Col D - Latest action
Col E - Latest date

So D2 put this formula =LOOKUP(E2,$C$2:$C$6,$B$2:$B$6)

in E2 put this formula =MAX(IF(--($A$2:$A$6=A2),$C$2:$C$6,""))

 

[mycarpark]

hmmm...it does not work...

id action date latest action latest date
k1 om 1/9/2008 tp 5/9/2008
e1 tp 2/9/2008 wo 2/9/2008
a12 wo 2/9/2008 wo 2/9/2008
k1 4 3/9/2008 tp 5/9/2008
a5 8 5/9/2008 tp 5/9/2008
k1 fu 5/9/2008 tp 5/9/2008
e13 tp 5/9/2008 s.AA 9/9/2008
e13 s.AA 9/9/2008 s.AA 9/9/2008

see the first line..
it should be
latest action = fu

 

[Teethless mama]

Try this:

In D2: =LOOKUP(2,1/($A$2:$A$6=$A2),B$2:B$6)

copy across to E2 and down