This proposal utilizes several helper columns.
On the Issued sheet column G makes the maintenance required match using: =ISNUMBER(MATCH(B2,MaintPartNumber!D$2:D$11,0))
Column H makes the maintenance done match using: =IF(G2=FALSE,"",AND(ISNUMBER(MATCH(B2,MaintCompReport!E$2:E$6,0)),ISNUMBER(MATCH(C2,MaintCompReport!F$2:F$6,0))))
Column I counts the number of times the item is listed on the MaintPartNumber sheet using: =IF(H2=FALSE,COUNTIFS(MaintPartNumber!D$2:D$11,B2),"")
Column J makes a cumulative count using: =IF(I2="","",SUM(MAX(J$1:J1),I2))
On the MissingMaintReport sheet column T lists the part #'s using: =IFERROR(INDEX(Issued!B$2:B$11,AGGREGATE(15,6,(ROW(B$2:B$11)-ROW(B$1))/(Issued!$J$2:$J$11>=ROWS($A$1:$A1))/(Issued!$J$2:$J$11<>""),1)),"")
Column S counts the consecutive times an item is listed using: =IF(A12="","",IF(T12<>T11,1,SUM(S11,1)))
Columns A:E are populated using a formula similar to that of column T
Column F is populated using: =IF(A12="","",INDEX(MaintPartNumber!A$2:A$11,AGGREGATE(15,6,(ROW(A$2:A$11)-ROW(A$1))/(MaintPartNumber!D$2:D$11=T12),S12)))
Let us know if you have any questions.
Bookmarks