Forum Discussion
Show distinct value based on multiple conditions
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")
Himadbeatz ,
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;"")