Alert if the travel activity is after the work activity
 

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"

Alert if the travel activity is after the work activity
 

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

"Go Bucks!!!" wrote:

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"

Alert if the travel activity is after the work activity
 

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","")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Go Bucks!!!" wrote:

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"

Alert if the travel activity is after the work activity
 

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

Test successful..

Thanks to you both.



"Luke M" wrote:

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","")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Go Bucks!!!" wrote:

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"