Alert if the travel activity is after the work activity

[Go Bucks!!!]

I need to identify (e.g. "alert") travel activities occuring after a work
activity for each unique travel event. My data looks like this...

Event Activity Start End
C090721 Travel alert 8/4/2009 16:59 8/4/2009 17:00
C090721 Work 8/4/2009 16:47 8/4/2009 16:59
C090721 Travel 8/4/2009 16:46 8/4/2009 16:47
C090722 Travel alert 8/3/2009 12:59 8/3/2009 13:00
C090722 Work 8/3/2009 11:00 8/3/2009 12:59
C090722 Travel 8/3/2009 8:00 8/3/2009 11:00

For travel event, C090721 the travel occuring after the work activity is
identified with "alert"

 

[Sam Wilson]

Is this always ordered by Event then by start date descending?

If so, assuming A2 is the start of your event data and B2 your activity
data, put this in C2:

=IF(AND(A2=A3,B2="Travel",B3="Work"),"alert","")

and copy down.

Sam

 

[Luke M]

Assuming you only have 1 Work entry per event code
=IF(AND(B2="Travel",SUMPRODUCT(--(A$2:A$100=A2),--(B$2:B$100="Work"),E$2:E$100)<=D2),"alert","")

 

[Go Bucks!!!]

I only have one work activity per event code so I am using Luke's.

Test successful..

Thanks to you both.