VLOOKUP and return complete row but with certain conditions

[Alfie092]

Hi,

I am new to the forums and I need some help please

I have several rows where a specific Event ID (Column A) occurs multiple times. What I want to do is have all the key information on just 1 row without having the Event ID being displayed on multiple rows.

As shown in the attached image, in column P, I have for removed the duplicates for each of the IDs and in cell Q2 I have used the formula =VLOOKUP($P2,$A$2:$L$38,COLUMN(B1),FALSE) and dragged across + down to populate/create an additional table which I will be working on. Based on the highlighted cell, only the Event ID which is being looked up within column P and the "COLUMN" lookup changes, e.g, COLUMN(B1), COLUMN(C1), COLUMN(D1).

This additional table appears to partially return what I am looking for. However, I need a formula to return the following on a single row for a specific Event ID:

1) Based from the Date column (Column C) I want to return the earliest date shown of a specific ID within the additional table created on the right. For example Event ID 211680 has dates 01/10/2018, 02/10/2018 and 03/10/2018 across multiple rows. I just want 01/10/2018 to be returned as it's the earliest date. However, with my formula it is incorrectly returning the date 02/10/2018, as shown in my attached image (cell R5). What formula do I use to ensure it's looking for the earlier date for a specific Event ID?

2) From the IR Code column (Column L) I want to return the IR code for a specific Event ID in terms of "priority" being shown in the following order: LOM, DAM2, DAM1, AOG, 41, 43. So for example, if in any of the rows for a specific Event ID, "LOM" does not appear, then it looks to return "DAM2", if that does not appear in any of the rows for a specific Event ID and DAM1 does, then "DAM1" is returned, as it's the highest priority code being shown. As shown with Event ID, it is returning the IR Code "41" (cell AA5), however, I want it to return "AOG" as it's the highest priority code. It appears my formula is only returning the values found within the first row of a specific Event ID. What can I do to ensure it returns the highest possible IR code? (Tried to explain this as simple as possible!

)

3) From the additional table I have created, the formula I have used returns all the columns, however, I only want to return specific columns. I want to return columns: B, C, D, G, H, I, J, K, L and M. I have tried just deleting the columns from the additional table created but then it ruins the whole table and returns incorrect values.

I would appreciate it if anyone could help me out please and I am willing to clarify anything that is unclear!

Thanks

 

[Alfie092]

Is someone kindly able to help me please, I am trying to figure it out myself for the past few hours but I am really struggling to get what I want ?

 

[AlanY]

It will be easier for the helpers to use the XL2BB add-in to post data, rather than an image. It saves us recreating your data.

XL2BB - Excel Range to BBCode
Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[Alfie092]

It will be easier for the helpers to use the XL2BB add-in to post data, rather than an image. It saves us recreating your data.

XL2BB - Excel Range to BBCode
Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

Hi, apologies for not doing that first time around! Please see the below in regards to my spreadsheet. I am unable to edit my original post so I cannot include it there.

Do you believe you would be able to assist me please?

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L		N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Event ID	A/C	Date	Station	Flight No	Type	Delay Minutes	Total Event Delay Minutes	Total Event Average Minutes	Event ID Occurences	Total Active Days of Event	IR Code				Event ID	A/C	Date	Station	Flight No	Type	Delay Minutes	Total Event Delay Minutes	Total Event Average Minutes	Event ID Occurences	Total Active Days of Event	IR Code
2	207649	G-EZUK	02/10/2018	MAN	EZY1919	D	5	5	5	1	0	41				207649	G-EZUK	02/10/2018	MAN	EZY1919	D	5	5	5	1	0	41
3	210491	OE-LKJ	03/10/2018	MXP	EZY2785	D	5	5	5	1	0	43				210491	OE-LKJ	03/10/2018	MXP	EZY2785	D	5	5	5	1	0	43
4	211359	G-EZIV	02/10/2018	NTE	EZY1708	D	10	10	10	1	0	43				211359	G-EZIV	02/10/2018	NTE	EZY1708	D	10	10	10	1	0	43
5	211680	OE-IZB	02/10/2018	LGW	EZY8113	D	10	30	10	3	1	41				211680	OE-IZB	02/10/2018	LGW	EZY8113	D	10	30	10	3	1	41
6	211680	OE-IZB	01/10/2018	ZRH	EZY8114	D	10	30	10	3	1	41				211816	OE-LKO	02/10/2018	SEN	OOS	D	690	330	165	2	-1	AOG
7	211680	OE-IZB	03/10/2018	BFS	EZY842	D	10	30	10	3	1	AOG				211818	G-EZBC	01/10/2018	LGW		D	735	735	735	1	0	AOG
8	211816	OE-LKO	02/10/2018	SEN	OOS	D	690	330	165	2	-1	AOG				211819	G-EZDA	01/10/2018	LGW	OOS	D	540	540	180	3	0	AOG
9	211816	OE-LKO	01/10/2018	SEN	EZY7381	D	0	330	165	2	-1	41				211820	G-EZWJ	01/10/2018	LGW	OOS	D	270	270	135	2	0	AOG
10	211818	G-EZBC	01/10/2018	LGW		D	735	735	735	1	0	AOG				211821	G-EZAI	01/10/2018	TLS		D	660	660	660	1	0	AOG
11	211819	G-EZDA	01/10/2018	LGW	OOS	D	540	540	180	3	0	AOG				211825	OE-LQV	01/10/2018	LGW	OOS	D	1080	3960	990	4	2	AOG
12	211819	G-EZDA	01/10/2018	LGW	EZY807	D	0	540	180	3	0	41				211826	OE-IVO	01/10/2018	BCN	EZY1076	D	20	20	20	1	0	41
13	211819	G-EZDA	01/10/2018	LGW	EZY865	D	0	540	180	3	0	41				211827	G-EZWC	01/10/2018	STR	`	D	144	144	144	1	0	41
14	211820	G-EZWJ	01/10/2018	LGW	OOS	D	270	270	135	2	0	AOG				211829	G-EZEY	01/10/2018	LGW	EZY8869	D	9	9	9	1	0	41
15	211820	G-EZWJ	01/10/2018	LGW	EZY8717	D	0	270	135	2	0	41				211831	G-EZAL	01/10/2018	LGW	EZY8243	D	13	13	13	1	0	41
16	211821	G-EZAI	01/10/2018	TLS		D	660	660	660	1	0	AOG				211833	G-EZGE	01/10/2018	SXF	OOS	D	150	150	150	1	0	LOM
17	211825	OE-LQV	01/10/2018	LGW	OOS	D	1080	3960	990	4	2	AOG				211834	OE-LKE	01/10/2018	MAN	EZY1985	D	4	4	4	1	0	41
18	211825	OE-LQV	01/10/2018	LGW	OOS	D	0	3960	990	4	2	AOG				211837	G-EZAI	01/10/2018	LIL	EZY1702	D	8	8	8	1	0	41
19	211825	OE-LQV	02/10/2018	LGW	AOG	D	1080	3960	990	4	2	AOG				211840	G-EZDD	01/10/2018	LGW	OOS	D	255	255	127.5	2	0	DAM2
20	211825	OE-LQV	03/10/2018	LGW	OOS	D	1080	3960	990	4	2	AOG				211843	OE-ICF	01/10/2018	OPO	OOS	D	300	300	150	2	0	DAM2
21	211826	OE-IVO	01/10/2018	BCN	EZY1076	D	20	20	20	1	0	41				211844	OE-LKG	01/10/2018	PMI	OOS	D	810	810	810	1	0	DAM1
22	211827	G-EZWC	01/10/2018	STR	`	D	144	144	144	1	0	41				211848	OE-INE	01/10/2018	ATH	EZY6062	D	48	48	48	1	0	41
23	211829	G-EZEY	01/10/2018	LGW	EZY8869	D	9	9	9	1	0	41				211851	G-EZGF	01/10/2018	LGW	EZY8189	D	9	9	9	1	0	41
24	211831	G-EZAL	01/10/2018	LGW	EZY8243	D	13	13	13	1	0	41				211854	OE-ICW	01/10/2018	BRS	EZY443	D	12	12	12	1	0	43
25	211833	G-EZGE	01/10/2018	SXF	OOS	D	150	150	150	1	0	LOM				211855	OE-LQI	01/10/2018	TLS	OOS	D	840	1740	580	3	1	AOG
26	211834	OE-LKE	01/10/2018	MAN	EZY1985	D	4	4	4	1	0	41
27	211837	G-EZAI	01/10/2018	LIL	EZY1702	D	8	8	8	1	0	41
28	211840	G-EZDD	01/10/2018	LGW	OOS	D	255	255	128	2	0	DAM2
29	211840	G-EZDD	01/10/2018	LGW	EZY8981	D	0	255	128	2	0	41
30	211843	OE-ICF	01/10/2018	OPO	OOS	D	300	300	150	2	0	DAM2
31	211843	OE-ICF	01/10/2018	OPO	EZY3772	D	0	300	150	2	0	52
32	211844	OE-LKG	01/10/2018	PMI	OOS	D	810	810	810	1	0	DAM1
33	211848	OE-INE	01/10/2018	ATH	EZY6062	D	48	48	48	1	0	41
34	211851	G-EZGF	01/10/2018	LGW	EZY8189	D	9	9	9	1	0	41
35	211854	OE-ICW	01/10/2018	BRS	EZY443	D	12	12	12	1	0	43
36	211855	OE-LQI	01/10/2018	TLS	OOS	D	840	1740	580	3	1	AOG
37	211855	OE-LQI	02/10/2018	TLS	OOS	D	540	1740	580	3	1	AOG
38	211855	OE-LQI	02/10/2018	TLS	EZY1474	D	0	1740	580	3	1	41
Sheet1

Cell Formulas
Range	Formula
H2:H38	H2	{=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))}
I2:I38	I2	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]
J2:J38	J2	=COUNTIF(A:A,[@[Event ID]])
K2:K38	K2	=LOOKUP(2,1/([Event ID]=A2),[Date])-INDEX([Date],MATCH(A2,[Event ID],0))
Q2:AA25	Q2	=VLOOKUP($P2,$A$2:$L$38,COLUMN(B1),FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[AlanY]

have a look of this, with an additional helper column M,

your cells in Cols H to K not showing up properly, on Col T I assume you want the max delay time, the other 4 cols should be similar

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Event ID	A/C	Date	Station	Flight No	Type	Delay Minutes	Total Event Delay Minutes	Total Event Average Minutes	Event ID Occurences	Total Active Days of Event	IR Code				Event ID	A/C	Date	Station	Delay Minutes	Total Event Delay Minutes	Total Event Average Minutes	Event ID Occurences	Total Active Days of Event	IR Code
2	207649	G-EZUK	02/10/2018	MAN	EZY1919	D	5	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A2),[Date])-INDEX([Date],MATCH(A2,[Event ID],0))	41	2			207649	G-EZUK	02/10/2018	MAN	5	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A2),[Date])-INDEX([Date],MATCH(A2,[Event ID],0))	41
3	210491	OE-LKJ	03/10/2018	MXP	EZY2785	D	5	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A3),[Date])-INDEX([Date],MATCH(A3,[Event ID],0))	43	1			210491	OE-LKJ	03/10/2018	MXP	5	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A3),[Date])-INDEX([Date],MATCH(A3,[Event ID],0))	43
4	211359	G-EZIV	02/10/2018	NTE	EZY1708	D	10	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A4),[Date])-INDEX([Date],MATCH(A4,[Event ID],0))	43	1			211359	G-EZIV	02/10/2018	NTE	10	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A4),[Date])-INDEX([Date],MATCH(A4,[Event ID],0))	43
5	211680	OE-IZB	02/10/2018	LGW	EZY8113	D	10	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A5),[Date])-INDEX([Date],MATCH(A5,[Event ID],0))	41	2			211680	OE-IZB	01/10/2018	LGW	10	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A5),[Date])-INDEX([Date],MATCH(A5,[Event ID],0))	AOG
6	211680	OE-IZB	01/10/2018	ZRH	EZY8114	D	10	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A6),[Date])-INDEX([Date],MATCH(A6,[Event ID],0))	41	2			211816	OE-LKO	01/10/2018	SEN	690	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A8),[Date])-INDEX([Date],MATCH(A8,[Event ID],0))	AOG
7	211680	OE-IZB	03/10/2018	BFS	EZY842	D	10	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A7),[Date])-INDEX([Date],MATCH(A7,[Event ID],0))	AOG	3			211818	G-EZBC	01/10/2018	LGW	735	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A10),[Date])-INDEX([Date],MATCH(A10,[Event ID],0))	AOG
8	211816	OE-LKO	02/10/2018	SEN	OOS	D	690	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A8),[Date])-INDEX([Date],MATCH(A8,[Event ID],0))	AOG	3			211819	G-EZDA	01/10/2018	LGW	540	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A11),[Date])-INDEX([Date],MATCH(A11,[Event ID],0))	AOG
9	211816	OE-LKO	01/10/2018	SEN	EZY7381	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A9),[Date])-INDEX([Date],MATCH(A9,[Event ID],0))	41	2			211820	G-EZWJ	01/10/2018	LGW	270	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A14),[Date])-INDEX([Date],MATCH(A14,[Event ID],0))	AOG
10	211818	G-EZBC	01/10/2018	LGW		D	735	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A10),[Date])-INDEX([Date],MATCH(A10,[Event ID],0))	AOG	3			211821	G-EZAI	01/10/2018	TLS	660	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A16),[Date])-INDEX([Date],MATCH(A16,[Event ID],0))	AOG
11	211819	G-EZDA	01/10/2018	LGW	OOS	D	540	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A11),[Date])-INDEX([Date],MATCH(A11,[Event ID],0))	AOG	3			211825	OE-LQV	01/08/2018	LGW	1080	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A17),[Date])-INDEX([Date],MATCH(A17,[Event ID],0))	AOG
12	211819	G-EZDA	01/10/2018	LGW	EZY807	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A12),[Date])-INDEX([Date],MATCH(A12,[Event ID],0))	41	2			211826	OE-IVO	01/10/2018	BCN	20	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A21),[Date])-INDEX([Date],MATCH(A21,[Event ID],0))	41
13	211819	G-EZDA	01/10/2018	LGW	EZY865	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A13),[Date])-INDEX([Date],MATCH(A13,[Event ID],0))	41	2			211827	G-EZWC	01/10/2018	STR	144	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A22),[Date])-INDEX([Date],MATCH(A22,[Event ID],0))	41
14	211820	G-EZWJ	01/10/2018	LGW	OOS	D	270	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A14),[Date])-INDEX([Date],MATCH(A14,[Event ID],0))	AOG	3			211829	G-EZEY	01/10/2018	LGW	9	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A23),[Date])-INDEX([Date],MATCH(A23,[Event ID],0))	41
15	211820	G-EZWJ	01/10/2018	LGW	EZY8717	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A15),[Date])-INDEX([Date],MATCH(A15,[Event ID],0))	41	2			211831	G-EZAL	01/10/2018	LGW	13	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A24),[Date])-INDEX([Date],MATCH(A24,[Event ID],0))	41
16	211821	G-EZAI	01/10/2018	TLS		D	660	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A16),[Date])-INDEX([Date],MATCH(A16,[Event ID],0))	AOG	3			211833	G-EZGE	01/10/2018	SXF	150	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A25),[Date])-INDEX([Date],MATCH(A25,[Event ID],0))	LOM
17	211825	OE-LQV	01/10/2018	LGW	OOS	D	1080	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A17),[Date])-INDEX([Date],MATCH(A17,[Event ID],0))	AOG	3			211834	OE-LKE	01/10/2018	MAN	4	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A26),[Date])-INDEX([Date],MATCH(A26,[Event ID],0))	41
18	211825	OE-LQV	01/08/2018	LGW	OOS	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A18),[Date])-INDEX([Date],MATCH(A18,[Event ID],0))	AOG	3			211837	G-EZAI	01/10/2018	LIL	8	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A27),[Date])-INDEX([Date],MATCH(A27,[Event ID],0))	41
19	211825	OE-LQV	02/10/2018	LGW	AOG	D	1080	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A19),[Date])-INDEX([Date],MATCH(A19,[Event ID],0))	AOG	3			211840	G-EZDD	01/10/2018	LGW	255	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A28),[Date])-INDEX([Date],MATCH(A28,[Event ID],0))	DAM2
20	211825	OE-LQV	03/10/2018	LGW	OOS	D	1080	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A20),[Date])-INDEX([Date],MATCH(A20,[Event ID],0))	AOG	3			211843	OE-ICF	01/09/2018	OPO	300	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A30),[Date])-INDEX([Date],MATCH(A30,[Event ID],0))	DAM2
21	211826	OE-IVO	01/10/2018	BCN	EZY1076	D	20	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A21),[Date])-INDEX([Date],MATCH(A21,[Event ID],0))	41	2			211844	OE-LKG	01/10/2018	PMI	810	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A32),[Date])-INDEX([Date],MATCH(A32,[Event ID],0))	DAM1
22	211827	G-EZWC	01/10/2018	STR	`	D	144	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A22),[Date])-INDEX([Date],MATCH(A22,[Event ID],0))	41	2			211848	OE-INE	01/10/2018	ATH	48	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A33),[Date])-INDEX([Date],MATCH(A33,[Event ID],0))	41
23	211829	G-EZEY	01/10/2018	LGW	EZY8869	D	9	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A23),[Date])-INDEX([Date],MATCH(A23,[Event ID],0))	41	2			211851	G-EZGF	01/10/2018	LGW	9	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A34),[Date])-INDEX([Date],MATCH(A34,[Event ID],0))	41
24	211831	G-EZAL	01/10/2018	LGW	EZY8243	D	13	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A24),[Date])-INDEX([Date],MATCH(A24,[Event ID],0))	41	2			211854	OE-ICW	01/10/2018	BRS	12	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A35),[Date])-INDEX([Date],MATCH(A35,[Event ID],0))	43
25	211833	G-EZGE	01/10/2018	SXF	OOS	D	150	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A25),[Date])-INDEX([Date],MATCH(A25,[Event ID],0))	LOM	6			211855	OE-LQI	01/10/2018	TLS	840	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A36),[Date])-INDEX([Date],MATCH(A36,[Event ID],0))	AOG
26	211834	OE-LKE	01/10/2018	MAN	EZY1985	D	4	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A26),[Date])-INDEX([Date],MATCH(A26,[Event ID],0))	41	2
27	211837	G-EZAI	01/10/2018	LIL	EZY1702	D	8	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A27),[Date])-INDEX([Date],MATCH(A27,[Event ID],0))	41	2
28	211840	G-EZDD	01/10/2018	LGW	OOS	D	255	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A28),[Date])-INDEX([Date],MATCH(A28,[Event ID],0))	DAM2	5
29	211840	G-EZDD	01/10/2018	LGW	EZY8981	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A29),[Date])-INDEX([Date],MATCH(A29,[Event ID],0))	41	2
30	211843	OE-ICF	01/09/2018	OPO	OOS	D	300	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A30),[Date])-INDEX([Date],MATCH(A30,[Event ID],0))	DAM2	5
31	211843	OE-ICF	01/10/2018	OPO	EZY3772	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A31),[Date])-INDEX([Date],MATCH(A31,[Event ID],0))	52	0
32	211844	OE-LKG	01/10/2018	PMI	OOS	D	810	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A32),[Date])-INDEX([Date],MATCH(A32,[Event ID],0))	DAM1	4
33	211848	OE-INE	01/10/2018	ATH	EZY6062	D	48	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A33),[Date])-INDEX([Date],MATCH(A33,[Event ID],0))	41	2
34	211851	G-EZGF	01/10/2018	LGW	EZY8189	D	9	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A34),[Date])-INDEX([Date],MATCH(A34,[Event ID],0))	41	2
35	211854	OE-ICW	01/10/2018	BRS	EZY443	D	12	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A35),[Date])-INDEX([Date],MATCH(A35,[Event ID],0))	43	1
36	211855	OE-LQI	01/10/2018	TLS	OOS	D	840	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A36),[Date])-INDEX([Date],MATCH(A36,[Event ID],0))	AOG	3
37	211855	OE-LQI	02/10/2018	TLS	OOS	D	540	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A37),[Date])-INDEX([Date],MATCH(A37,[Event ID],0))	AOG	3
38	211855	OE-LQI	02/10/2018	TLS	EZY1474	D	0	=SUMIFS($G:$G,$A:$A,[@[Event ID]])+(360*[@[Total Active Days of Event]])*(AND(COUNTIFS($A:$A,[@[Event ID]],G:G,">"&60),SUM(COUNTIFS($A:$A,[@[Event ID]],L:L,{"AOG","AOG4","DAM1","DAM2"}))))	=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]	=COUNTIF(A:A,[@[Event ID]])	=LOOKUP(2,1/([Event ID]=A38),[Date])-INDEX([Date],MATCH(A38,[Event ID],0))	41	2
Sheet3

Cell Formulas
Range	Formula
Q2:Q25, S2:S25, U2:X25	Q2	=INDEX($A$1:$K$38,MATCH($P2,$A$1:$A$38,0),MATCH(Q$1,$A$1:$K$1,0))
R2:R25	R2	{=SMALL(IF($A$2:$A$38=P2,$C$2:$C$38),1)}
T2:T25	T2	=SUMPRODUCT(LARGE(($A$2:$A$38=P2)*($G$2:$G$38),1))
Y2:Y25	Y2	=CHOOSE(SUMPRODUCT(LARGE(($A$2:$A$38=P2)*($M$2:$M$38),1)),43,41,"AOG","DAM1","DAM2","LOM")
M2:M38	M2	=IFERROR(7-MATCH(L2,{"LOM";"DAM2";"DAM1";"AOG";41;43},0),0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Alfie092]

have a look of this, with an additional helper column M,

your cells in Cols H to K not showing up properly, on Col T I assume you want the max delay time, the other 4 cols should be similar

Thank you for this! However, are you kindly able to amend the formulas in column M and Y for me please? I have tried it myself but it comes out incorrectly.

Within my full spreadsheet (which is extremely large) it also has more IR codes, could you please include this for me?

The codes are AOG4 (just below "AOG") in priority and also IR codes 42, 44, 45, 51, 52 (priority in ascending order), which are not within the formulas you provided. Full list of number IR codes is AOG4 and also 41, 42, 43, 44, 45, 51, 52. So would appreciate it if you can arrange the numbers in that order please, along with AOG4 being a higher priority than the numbers.

Thank you so much and would really appreciate it if you could help

 

[AlanY]

since you now have 12 IR codes it's better to use a lookup table (Col N) and I've amended the formula to suit.

Note: you need to confirm the array formula in Cols R & Y with shift-control-enter together.

Book1
	A	B	C									L	M	N	O	P	Q	R	S	T					Y
1	Event ID	A/C	Date									IR Code		lookup table		Event ID	A/C	Date	Station	Delay Minutes					IR Code
2	207649	G-EZUK	02/10/2018									41	6	LOM		207649	G-EZUK	02/10/2018	MAN	5					41
3	210491	OE-LKJ	03/10/2018									43	8	DAM2		210491	OE-LKJ	03/10/2018	MXP	5					43
4	211359	G-EZIV	02/10/2018									43	8	DAM1		211359	G-EZIV	02/10/2018	NTE	10					43
5	211680	OE-IZB	02/10/2018									41	6	AOG		211680	OE-IZB	01/10/2018	LGW	10					AOG
6	211680	OE-IZB	01/10/2018									41	6	AOG4		211816	OE-LKO	01/10/2018	SEN	690					AOG
7	211680	OE-IZB	03/10/2018									AOG	4	41		211818	G-EZBC	01/10/2018	LGW	735					AOG
8	211816	OE-LKO	02/10/2018									AOG	4	42		211819	G-EZDA	01/10/2018	LGW	540					AOG
9	211816	OE-LKO	01/10/2018									41	6	43		211820	G-EZWJ	01/10/2018	LGW	270					AOG
10	211818	G-EZBC	01/10/2018									AOG	4	44		211821	G-EZAI	01/10/2018	TLS	660					AOG
11	211819	G-EZDA	01/10/2018									AOG	4	45		211825	OE-LQV	01/08/2018	LGW	1080					AOG
12	211819	G-EZDA	01/10/2018									41	6	51		211826	OE-IVO	01/10/2018	BCN	20					41
13	211819	G-EZDA	01/10/2018									41	6	52		211827	G-EZWC	01/10/2018	STR	144					41
14	211820	G-EZWJ	01/10/2018									AOG	4			211829	G-EZEY	01/10/2018	LGW	9					41
15	211820	G-EZWJ	01/10/2018									41	6			211831	G-EZAL	01/10/2018	LGW	13					41
16	211821	G-EZAI	01/10/2018									AOG	4			211833	G-EZGE	01/10/2018	SXF	150					LOM
17	211825	OE-LQV	01/10/2018									AOG	4			211834	OE-LKE	01/10/2018	MAN	4					41
18	211825	OE-LQV	01/08/2018									AOG	4			211837	G-EZAI	01/10/2018	LIL	8					41
19	211825	OE-LQV	02/10/2018									AOG	4			211840	G-EZDD	01/10/2018	LGW	255					DAM2
20	211825	OE-LQV	03/10/2018									AOG	4			211843	OE-ICF	01/09/2018	OPO	300					DAM2
21	211826	OE-IVO	01/10/2018									41	6			211844	OE-LKG	01/10/2018	PMI	810					DAM1
22	211827	G-EZWC	01/10/2018									41	6			211848	OE-INE	01/10/2018	ATH	48					41
23	211829	G-EZEY	01/10/2018									41	6			211851	G-EZGF	01/10/2018	LGW	9					41
24	211831	G-EZAL	01/10/2018									41	6			211854	OE-ICW	01/10/2018	BRS	12					43
25	211833	G-EZGE	01/10/2018									LOM	1			211855	OE-LQI	01/10/2018	TLS	840					AOG
26	211834	OE-LKE	01/10/2018									41	6
27	211837	G-EZAI	01/10/2018									41	6
28	211840	G-EZDD	01/10/2018									DAM2	2
29	211840	G-EZDD	01/10/2018									41	6
30	211843	OE-ICF	01/09/2018									DAM2	2
31	211843	OE-ICF	01/10/2018									52	12
32	211844	OE-LKG	01/10/2018									DAM1	3
33	211848	OE-INE	01/10/2018									41	6
34	211851	G-EZGF	01/10/2018									41	6
35	211854	OE-ICW	01/10/2018									43	8
36	211855	OE-LQI	01/10/2018									AOG	4
37	211855	OE-LQI	02/10/2018									AOG	4
38	211855	OE-LQI	02/10/2018									41	6
Sheet2

Cell Formulas
Range	Formula
Q2:Q25, S2:S25	Q2	=INDEX($A$1:$K$38,MATCH($P2,$A$1:$A$38,0),MATCH(Q$1,$A$1:$K$1,0))
R2:R25	R2	{=SMALL(IF($A$2:$A$38=P2,$C$2:$C$38),1)}
T2:T25	T2	=SUMPRODUCT(LARGE(($A$2:$A$38=P2)*($G$2:$G$38),1))
Y2:Y25	Y2	{=INDEX($N$2:$N$13,SMALL(IF($A$2:$A$38=P2,$M$2:$M$38),1))}
M2:M38	M2	=MATCH(L2,$N$2:$N$13,0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.