Alert if the travel activity is after the work activity

  • Thread starter Thread starter Go Bucks!!!
  • Start date Start date
G

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"
 
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
 
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","")
 
I only have one work activity per event code so I am using Luke's.

Test successful..

Thanks to you both.
 
Back
Top