How to reflect 1 or 0 based on data on other cells?-CodePudding

In the range of A1:W14, I have the delivery data. Based on the data, I have to check the performance within the range A18:W31. In Delivery performance table, Dispatch Column, I need to bring the dispatch volume from the Delivery Data table. That can be done by doing cell linking but considering the dynamic data changes in Delivery performance table I can not do that.

Based on SLA column, the performance will be reflected accordingly. Here, D-0 means the delivery on the dispatched day. D 1 is the next day of dispatch and D 2 is the 2nd Next day of dispatch.

If the SLA of a customer is D-0 and they receive products as per SLA, value of D-0 on the dispatched day will be 1 else 0. For instance, AAA's products dispatched on 1st Jan and AAA received on the same day as per SLA, so the value of D-0 on 1st Jan is 1 (cell E22). In another example, BBB's product volume is 200 and dispatched on 1st Jan. As per SLA, delivery was supposed to be done on same day but delivered on next day (i.e. D 1) of dispatch. So the value of D-0 on 1st Jan is 0 (Cell E23).

As an exceptional example, III's ordered volume 200 unit was dispatched on 1st Jan. As per SLA, the order was supposed to deliver on 2nd Jan (D 1). But due to efficiency and urgency, it was delivered on D-0. In that case, performance will be 1.

I am trying to replace the value by using formula or any automated ways. To get the spreadsheet,

CodePudding user response：

E22:

=ARRAYFORMULA(SPLIT(REPT(" ×", REGEXEXTRACT($C5:$C14, "\d "))&
 IF(TRIM(FLATTEN(QUERY(TRANSPOSE(IFNA(VLOOKUP(IF((1*REGEXEXTRACT($C5:$C14, "\d "))>=
 SEQUENCE(1, 4, 0), ROW(D5:D14)&"×"&D5:D14&"×"&SEQUENCE(1, 4, 0) D1, ), 
 FLATTEN(IF(FILTER(D5:$W14, D3:$W3="delivery")="",,
 FILTER(ROW(D5:D14)&"×"&D5:$W14&"×"&D1:$W1, D3:$W3="delivery"))), 1, 0))),,
 9^9)))="", 0, 1), "×", 1))

J22:

=ARRAYFORMULA(SPLIT(REPT(" ×", REGEXEXTRACT($C5:$C14, "\d "))&
 IF(TRIM(FLATTEN(QUERY(TRANSPOSE(IFNA(VLOOKUP(IF((1*REGEXEXTRACT($C5:$C14, "\d "))>=
 SEQUENCE(1, 4, 0), ROW(H5:H14)&"×"&H5:H14&"×"&SEQUENCE(1, 4, 0) H1, ), 
 FLATTEN(IF(FILTER(H5:$W14, H3:$W3="delivery")="",,
 FILTER(ROW(H5:H14)&"×"&H5:$W14&"×"&H1:$W1, H3:$W3="delivery"))), 1, 0))),,
 9^9)))="", 0, 1), "×", 1))

etc.