Show distinct value based on multiple conditions

[madbeatz]

I have a excel data sheet which is an extract from a support tool. The extract is shown in the picture below.

Column A - Ticket ID. The ticket ID is unique to each case but appears multiple time as diiferent actions are performed on the ticket for example ticket created, ticket escalated and so on

Column B - Created Date.  - Date and time the ticket was created and will not change

Column C - Transition date - Date and time the ticket is transition to different state or action, eg. escalated, closed etc

Column D - Transition Time - Difference between created date and transition date

Column E - Escalation Team

Column F - Which team escalated to escalation team (Column E)

The ask? In a new column G, display "1" when the first distinct ticket ID is found when the ticket is assign to L2 support by either the Product Team or the Development Team

The formulae I used below came close, but stumbled when the condition was met but marked the same ticket ID twice (hence not distinct) eg ticket ID 20026 as below. Any help would be appreciated. 

=IF((E2="L2 Support")*(OR(F2="Product Team", F2="Development Team"))*(COUNTIFS(A$2:A2, A2, E$2:E2, "L2 Support", F$2:F2, "<>Product Team", F$2:F2, "<>Development Team")=0), 1, "0")

[#2 · Accepted solution]

Hi@madbeatz ,
you can try this formula. This should produce your result:

=IF(AND(E2="L2 Support";OR(F2="Development Team";F2="Product Team");SUMPRODUCT(--(A$2:A2&E$2:E2=A2&E2))=1);1;"")

[#3]

@dscheikey Tested against a larger data set and this works! thank you!