Thanks in advance for your help. I have a spreadsheet of 30,000 test results - each on a unique row. Column A has the device name, B = test date, and C = test status (PA for pass or FL for fail). Each device name has multiple rows associated with it for passing and sometimes a failed test. I need to develop a formula in column D to identify/flag which devices have a failed test, and NO passing test after the date of that failed test. Specifically, if a device fails a test, it must be re-tested and pass a test within 120 days. If the device fails a test and we have no passing tests after that date, then it's a violation. Any ideas on how to do this? I've been thinking on it for a couple of weeks and can't get it right. Thanks!
Paste this in a new sheet:
| Name | Test date | Test status | Violations | Next pass row | Name | Test date | Test status | Days count |
|---|---|---|---|---|---|---|---|---|
| A | 11/10/2022 | FL | =IF(C2="FL",IF(COUNTIFS(C:C,"PA",A:A,A2,B:B,">"&B2,B:B,"<="&B2+120)>=1,"ok","violation"),"") | =IF(D2="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A2)/(B:B>B2)/(B:B<=B2+120),1),"") | =IF(E2="","",INDEX(A:A,$E2)) | =IF(F2="","",INDEX(B:B,$E2)) | =IF(G2="","",INDEX(C:C,$E2)) | =IF(E2="","",G2-B2) |
| B | 01/02/2021 | PA | =IF(C3="FL",IF(COUNTIFS(C:C,"PA",A:A,A3,B:B,">"&B3,B:B,"<="&B3+120)>=1,"ok","violation"),"") | =IF(D3="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A3)/(B:B>B3)/(B:B<=B3+120),1),"") | =IF(E3="","",INDEX(A:A,$E3)) | =IF(F3="","",INDEX(B:B,$E3)) | =IF(G3="","",INDEX(C:C,$E3)) | =IF(E3="","",G3-B3) |
| C | 01/01/2022 | PA | =IF(C4="FL",IF(COUNTIFS(C:C,"PA",A:A,A4,B:B,">"&B4,B:B,"<="&B4+120)>=1,"ok","violation"),"") | =IF(D4="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A4)/(B:B>B4)/(B:B<=B4+120),1),"") | =IF(E4="","",INDEX(A:A,$E4)) | =IF(F4="","",INDEX(B:B,$E4)) | =IF(G4="","",INDEX(C:C,$E4)) | =IF(E4="","",G4-B4) |
| A | 09/12/2022 | FL | =IF(C5="FL",IF(COUNTIFS(C:C,"PA",A:A,A5,B:B,">"&B5,B:B,"<="&B5+120)>=1,"ok","violation"),"") | =IF(D5="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A5)/(B:B>B5)/(B:B<=B5+120),1),"") | =IF(E5="","",INDEX(A:A,$E5)) | =IF(F5="","",INDEX(B:B,$E5)) | =IF(G5="","",INDEX(C:C,$E5)) | =IF(E5="","",G5-B5) |
| B | 01/03/2021 | FL | =IF(C6="FL",IF(COUNTIFS(C:C,"PA",A:A,A6,B:B,">"&B6,B:B,"<="&B6+120)>=1,"ok","violation"),"") | =IF(D6="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A6)/(B:B>B6)/(B:B<=B6+120),1),"") | =IF(E6="","",INDEX(A:A,$E6)) | =IF(F6="","",INDEX(B:B,$E6)) | =IF(G6="","",INDEX(C:C,$E6)) | =IF(E6="","",G6-B6) |
| A | 07/02/2023 | PA | =IF(C7="FL",IF(COUNTIFS(C:C,"PA",A:A,A7,B:B,">"&B7,B:B,"<="&B7+120)>=1,"ok","violation"),"") | =IF(D7="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A7)/(B:B>B7)/(B:B<=B7+120),1),"") | =IF(E7="","",INDEX(A:A,$E7)) | =IF(F7="","",INDEX(B:B,$E7)) | =IF(G7="","",INDEX(C:C,$E7)) | =IF(E7="","",G7-B7) |
| D | 01/07/2023 | FL | =IF(C8="FL",IF(COUNTIFS(C:C,"PA",A:A,A8,B:B,">"&B8,B:B,"<="&B8+120)>=1,"ok","violation"),"") | =IF(D8="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A8)/(B:B>B8)/(B:B<=B8+120),1),"") | =IF(E8="","",INDEX(A:A,$E8)) | =IF(F8="","",INDEX(B:B,$E8)) | =IF(G8="","",INDEX(C:C,$E8)) | =IF(E8="","",G8-B8) |
| B | 30/06/2021 | PA | =IF(C9="FL",IF(COUNTIFS(C:C,"PA",A:A,A9,B:B,">"&B9,B:B,"<="&B9+120)>=1,"ok","violation"),"") | =IF(D9="ok",AGGREGATE(15,6,ROW(A:A)/(C:C="PA")/(A:A=A9)/(B:B>B9)/(B:B<=B9+120),1),"") | =IF(E9="","",INDEX(A:A,$E9)) | =IF(F9="","",INDEX(B:B,$E9)) | =IF(G9="","",INDEX(C:C,$E9)) | =IF(E9="","",G9-B9) |
D:D contains the formulas you are looking for. E:I contains extra formulas that return the data of the next passed test (if it is a thing). These extra formulas might be heavy if applied on 30000 rows (column E beign the main problem), so you might end up not using them. Still, you might use them on single rows. The formulas in column D:D should be reasonably fast. Still 30000 rows are still 30000 rows. A VBA based solution might be better suited.