VLOOKUP and return complete row but with certain conditions

Alfie092

New Member
Joined
Jan 21, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I am new to the forums and I need some help please
smile.gif


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!
smile.gif
)

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!
smile.gif


Thanks
 

Attachments

  • Capture8.PNG
    Capture8.PNG
    108.9 KB · Views: 9

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Alfie092

New Member
Joined
Jan 21, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

www.mrexcel.com

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

Alfie092

New Member
Joined
Jan 21, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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.

www.mrexcel.com

XL2BB - Excel Range to BBCode
Convert selected range to BBCode to include as a snapshot in your question.
www.mrexcel.com
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
ABCDEFGHIJKLNOPQRSTUVWXYZAA
1Event IDA/CDateStationFlight NoTypeDelay MinutesTotal Event Delay MinutesTotal Event Average MinutesEvent ID OccurencesTotal Active Days of EventIR CodeEvent IDA/CDateStationFlight NoTypeDelay MinutesTotal Event Delay MinutesTotal Event Average MinutesEvent ID OccurencesTotal Active Days of EventIR Code
2207649G-EZUK02/10/2018MANEZY1919D5551041207649G-EZUK02/10/2018MANEZY1919D5551041
3210491OE-LKJ03/10/2018MXPEZY2785D5551043210491OE-LKJ03/10/2018MXPEZY2785D5551043
4211359G-EZIV02/10/2018NTEEZY1708D1010101043211359G-EZIV02/10/2018NTEEZY1708D1010101043
5211680OE-IZB02/10/2018LGWEZY8113D1030103141211680OE-IZB02/10/2018LGWEZY8113D1030103141
6211680OE-IZB01/10/2018ZRHEZY8114D1030103141211816OE-LKO02/10/2018SENOOSD6903301652-1AOG
7211680OE-IZB03/10/2018BFSEZY842D10301031AOG211818G-EZBC01/10/2018LGW D73573573510AOG
8211816OE-LKO02/10/2018SENOOSD6903301652-1AOG211819G-EZDA01/10/2018LGWOOSD54054018030AOG
9211816OE-LKO01/10/2018SENEZY7381D03301652-141211820G-EZWJ01/10/2018LGWOOSD27027013520AOG
10211818G-EZBC01/10/2018LGWD73573573510AOG211821G-EZAI01/10/2018TLS D66066066010AOG
11211819G-EZDA01/10/2018LGWOOSD54054018030AOG211825OE-LQV01/10/2018LGWOOSD1080396099042AOG
12211819G-EZDA01/10/2018LGWEZY807D05401803041211826OE-IVO01/10/2018BCNEZY1076D2020201041
13211819G-EZDA01/10/2018LGWEZY865D05401803041211827G-EZWC01/10/2018STR`D1441441441041
14211820G-EZWJ01/10/2018LGWOOSD27027013520AOG211829G-EZEY01/10/2018LGWEZY8869D9991041
15211820G-EZWJ01/10/2018LGWEZY8717D02701352041211831G-EZAL01/10/2018LGWEZY8243D1313131041
16211821G-EZAI01/10/2018TLSD66066066010AOG211833G-EZGE01/10/2018SXFOOSD15015015010LOM
17211825OE-LQV01/10/2018LGWOOSD1080396099042AOG211834OE-LKE01/10/2018MANEZY1985D4441041
18211825OE-LQV01/10/2018LGWOOSD0396099042AOG211837G-EZAI01/10/2018LILEZY1702D8881041
19211825OE-LQV02/10/2018LGWAOGD1080396099042AOG211840G-EZDD01/10/2018LGWOOSD255255127.520DAM2
20211825OE-LQV03/10/2018LGWOOSD1080396099042AOG211843OE-ICF01/10/2018OPOOOSD30030015020DAM2
21211826OE-IVO01/10/2018BCNEZY1076D2020201041211844OE-LKG01/10/2018PMIOOSD81081081010DAM1
22211827G-EZWC01/10/2018STR`D1441441441041211848OE-INE01/10/2018ATHEZY6062D4848481041
23211829G-EZEY01/10/2018LGWEZY8869D9991041211851G-EZGF01/10/2018LGWEZY8189D9991041
24211831G-EZAL01/10/2018LGWEZY8243D1313131041211854OE-ICW01/10/2018BRSEZY443D1212121043
25211833G-EZGE01/10/2018SXFOOSD15015015010LOM211855OE-LQI01/10/2018TLSOOSD840174058031AOG
26211834OE-LKE01/10/2018MANEZY1985D4441041
27211837G-EZAI01/10/2018LILEZY1702D8881041
28211840G-EZDD01/10/2018LGWOOSD25525512820DAM2
29211840G-EZDD01/10/2018LGWEZY8981D02551282041
30211843OE-ICF01/10/2018OPOOOSD30030015020DAM2
31211843OE-ICF01/10/2018OPOEZY3772D03001502052
32211844OE-LKG01/10/2018PMIOOSD81081081010DAM1
33211848OE-INE01/10/2018ATHEZY6062D4848481041
34211851G-EZGF01/10/2018LGWEZY8189D9991041
35211854OE-ICW01/10/2018BRSEZY443D1212121043
36211855OE-LQI01/10/2018TLSOOSD840174058031AOG
37211855OE-LQI02/10/2018TLSOOSD540174058031AOG
38211855OE-LQI02/10/2018TLSEZY1474D017405803141
Sheet1
Cell Formulas
RangeFormula
H2:H38H2{=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:I38I2=[@[Total Event Delay Minutes]]/[@[Event ID Occurences]]
J2:J38J2=COUNTIF(A:A,[@[Event ID]])
K2:K38K2=LOOKUP(2,1/([Event ID]=A2),[Date])-INDEX([Date],MATCH(A2,[Event ID],0))
Q2:AA25Q2=VLOOKUP($P2,$A$2:$L$38,COLUMN(B1),FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMNOPQRSTUVWXY
1Event IDA/CDateStationFlight NoTypeDelay MinutesTotal Event Delay MinutesTotal Event Average MinutesEvent ID OccurencesTotal Active Days of EventIR CodeEvent IDA/CDateStationDelay MinutesTotal Event Delay MinutesTotal Event Average MinutesEvent ID OccurencesTotal Active Days of EventIR Code
2207649G-EZUK02/10/2018MANEZY1919D5=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))412207649G-EZUK02/10/2018MAN5=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
3210491OE-LKJ03/10/2018MXPEZY2785D5=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))431210491OE-LKJ03/10/2018MXP5=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
4211359G-EZIV02/10/2018NTEEZY1708D10=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))431211359G-EZIV02/10/2018NTE10=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
5211680OE-IZB02/10/2018LGWEZY8113D10=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))412211680OE-IZB01/10/2018LGW10=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
6211680OE-IZB01/10/2018ZRHEZY8114D10=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))412211816OE-LKO01/10/2018SEN690=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
7211680OE-IZB03/10/2018BFSEZY842D10=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))AOG3211818G-EZBC01/10/2018LGW735=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
8211816OE-LKO02/10/2018SENOOSD690=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))AOG3211819G-EZDA01/10/2018LGW540=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
9211816OE-LKO01/10/2018SENEZY7381D0=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))412211820G-EZWJ01/10/2018LGW270=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
10211818G-EZBC01/10/2018LGWD735=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))AOG3211821G-EZAI01/10/2018TLS660=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
11211819G-EZDA01/10/2018LGWOOSD540=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))AOG3211825OE-LQV01/08/2018LGW1080=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
12211819G-EZDA01/10/2018LGWEZY807D0=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))412211826OE-IVO01/10/2018BCN20=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
13211819G-EZDA01/10/2018LGWEZY865D0=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))412211827G-EZWC01/10/2018STR144=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
14211820G-EZWJ01/10/2018LGWOOSD270=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))AOG3211829G-EZEY01/10/2018LGW9=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
15211820G-EZWJ01/10/2018LGWEZY8717D0=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))412211831G-EZAL01/10/2018LGW13=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
16211821G-EZAI01/10/2018TLSD660=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))AOG3211833G-EZGE01/10/2018SXF150=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
17211825OE-LQV01/10/2018LGWOOSD1080=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))AOG3211834OE-LKE01/10/2018MAN4=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
18211825OE-LQV01/08/2018LGWOOSD0=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))AOG3211837G-EZAI01/10/2018LIL8=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
19211825OE-LQV02/10/2018LGWAOGD1080=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))AOG3211840G-EZDD01/10/2018LGW255=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
20211825OE-LQV03/10/2018LGWOOSD1080=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))AOG3211843OE-ICF01/09/2018OPO300=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
21211826OE-IVO01/10/2018BCNEZY1076D20=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))412211844OE-LKG01/10/2018PMI810=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
22211827G-EZWC01/10/2018STR`D144=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))412211848OE-INE01/10/2018ATH48=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
23211829G-EZEY01/10/2018LGWEZY8869D9=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))412211851G-EZGF01/10/2018LGW9=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
24211831G-EZAL01/10/2018LGWEZY8243D13=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))412211854OE-ICW01/10/2018BRS12=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
25211833G-EZGE01/10/2018SXFOOSD150=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))LOM6211855OE-LQI01/10/2018TLS840=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
26211834OE-LKE01/10/2018MANEZY1985D4=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))412
27211837G-EZAI01/10/2018LILEZY1702D8=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))412
28211840G-EZDD01/10/2018LGWOOSD255=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))DAM25
29211840G-EZDD01/10/2018LGWEZY8981D0=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))412
30211843OE-ICF01/09/2018OPOOOSD300=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))DAM25
31211843OE-ICF01/10/2018OPOEZY3772D0=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))520
32211844OE-LKG01/10/2018PMIOOSD810=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))DAM14
33211848OE-INE01/10/2018ATHEZY6062D48=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))412
34211851G-EZGF01/10/2018LGWEZY8189D9=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))412
35211854OE-ICW01/10/2018BRSEZY443D12=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))431
36211855OE-LQI01/10/2018TLSOOSD840=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))AOG3
37211855OE-LQI02/10/2018TLSOOSD540=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))AOG3
38211855OE-LQI02/10/2018TLSEZY1474D0=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))412
Sheet3
Cell Formulas
RangeFormula
Q2:Q25, S2:S25, U2:X25Q2=INDEX($A$1:$K$38,MATCH($P2,$A$1:$A$38,0),MATCH(Q$1,$A$1:$K$1,0))
R2:R25R2{=SMALL(IF($A$2:$A$38=P2,$C$2:$C$38),1)}
T2:T25T2=SUMPRODUCT(LARGE(($A$2:$A$38=P2)*($G$2:$G$38),1))
Y2:Y25Y2=CHOOSE(SUMPRODUCT(LARGE(($A$2:$A$38=P2)*($M$2:$M$38),1)),43,41,"AOG","DAM1","DAM2","LOM")
M2:M38M2=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

New Member
Joined
Jan 21, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
ABCLMNOPQRSTY
1Event IDA/CDateIR Codelookup tableEvent IDA/CDateStationDelay MinutesIR Code
2207649G-EZUK02/10/2018416LOM207649G-EZUK02/10/2018MAN541
3210491OE-LKJ03/10/2018438DAM2210491OE-LKJ03/10/2018MXP543
4211359G-EZIV02/10/2018438DAM1211359G-EZIV02/10/2018NTE1043
5211680OE-IZB02/10/2018416AOG211680OE-IZB01/10/2018LGW10AOG
6211680OE-IZB01/10/2018416AOG4211816OE-LKO01/10/2018SEN690AOG
7211680OE-IZB03/10/2018AOG441211818G-EZBC01/10/2018LGW735AOG
8211816OE-LKO02/10/2018AOG442211819G-EZDA01/10/2018LGW540AOG
9211816OE-LKO01/10/201841643211820G-EZWJ01/10/2018LGW270AOG
10211818G-EZBC01/10/2018AOG444211821G-EZAI01/10/2018TLS660AOG
11211819G-EZDA01/10/2018AOG445211825OE-LQV01/08/2018LGW1080AOG
12211819G-EZDA01/10/201841651211826OE-IVO01/10/2018BCN2041
13211819G-EZDA01/10/201841652211827G-EZWC01/10/2018STR14441
14211820G-EZWJ01/10/2018AOG4211829G-EZEY01/10/2018LGW941
15211820G-EZWJ01/10/2018416211831G-EZAL01/10/2018LGW1341
16211821G-EZAI01/10/2018AOG4211833G-EZGE01/10/2018SXF150LOM
17211825OE-LQV01/10/2018AOG4211834OE-LKE01/10/2018MAN441
18211825OE-LQV01/08/2018AOG4211837G-EZAI01/10/2018LIL841
19211825OE-LQV02/10/2018AOG4211840G-EZDD01/10/2018LGW255DAM2
20211825OE-LQV03/10/2018AOG4211843OE-ICF01/09/2018OPO300DAM2
21211826OE-IVO01/10/2018416211844OE-LKG01/10/2018PMI810DAM1
22211827G-EZWC01/10/2018416211848OE-INE01/10/2018ATH4841
23211829G-EZEY01/10/2018416211851G-EZGF01/10/2018LGW941
24211831G-EZAL01/10/2018416211854OE-ICW01/10/2018BRS1243
25211833G-EZGE01/10/2018LOM1211855OE-LQI01/10/2018TLS840AOG
26211834OE-LKE01/10/2018416
27211837G-EZAI01/10/2018416
28211840G-EZDD01/10/2018DAM22
29211840G-EZDD01/10/2018416
30211843OE-ICF01/09/2018DAM22
31211843OE-ICF01/10/20185212
32211844OE-LKG01/10/2018DAM13
33211848OE-INE01/10/2018416
34211851G-EZGF01/10/2018416
35211854OE-ICW01/10/2018438
36211855OE-LQI01/10/2018AOG4
37211855OE-LQI02/10/2018AOG4
38211855OE-LQI02/10/2018416
Sheet2
Cell Formulas
RangeFormula
Q2:Q25, S2:S25Q2=INDEX($A$1:$K$38,MATCH($P2,$A$1:$A$38,0),MATCH(Q$1,$A$1:$K$1,0))
R2:R25R2{=SMALL(IF($A$2:$A$38=P2,$C$2:$C$38),1)}
T2:T25T2=SUMPRODUCT(LARGE(($A$2:$A$38=P2)*($G$2:$G$38),1))
Y2:Y25Y2{=INDEX($N$2:$N$13,SMALL(IF($A$2:$A$38=P2,$M$2:$M$38),1))}
M2:M38M2=MATCH(L2,$N$2:$N$13,0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,714
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top