Sale Order Fulfillment Date To Be Captured

Dear Friends,

In the Attached Excel File….

Column A Contains “Product” Code

Column B Contains Sale “Order” Reference

Column C Contains Sale “Order Qty”

Column F – There is a Table which contains day wise production Output details…

Support Required: -

Column D “Order Completion Date”

Cumulative Sale Order Qty Vs. Cumulative Production Schedule Qty which date gets >=0, That date should be captured in Column D

Even after considering, complete cumulative production schedule qty, If the Order qty not getting fulfilled, that line should be highlighted as “Shortage”

Note:
1. With my limited Knowledge, I could achieve this by doing some sorting of column and creating additional Rows, Column with cumulative qty and achieve this…
2. But I would like to achieve subjected requirement with a Single Formula in Column D

Thanks & Regards,
Rajeshkumar R

One way:

=LET(r,COLUMN($I$17:$AM$117),N,$H$18:$H$22,St,$I$18:$I$22,Mi,MIN(IF(SUBTOTAL(9,OFFSET(INDEX(St,MATCH(A3,N,0)),0,0,1,r-MIN(r)+1))>=SUMIF(A$3:A3,A3,C$3:C3),r)),IF(Mi=0,"Shortage",INDEX($17:$17,Mi)))

Please try

=LET(p,A$3:A3,q,C$3:C3,ps,$F$18:$F$22,qs,$G$18:$AK$22,d,$G$17:$AK$17,pl,INDEX(p,ROWS(p)),ql,SUMIFS(q,p,pl),
XLOOKUP(ql,MMULT(INDEX(qs,MATCH(pl,ps,),),--(d>=TRANSPOSE(d))),d,"Shortage",1))

or Spill with LAMBDA

=LET(p,A3:A16,q,C3:C16,ps,F18:F22,qs,G18:AK22,d,G17:AK17,r,SEQUENCE(ROWS(p)),c,MIN(COLUMN(qs)),
qa,SCAN(0,qs,LAMBDA(i,v,IF(COLUMN(v)=c,v,i+v))),qo,MMULT((p=TRANSPOSE(p))*(r>=TRANSPOSE(r)),q),
by,BYROW(r,LAMBDA(x,XLOOKUP(INDEX(qo,x),FILTER(qa,ps=INDEX(p,x)),d,"Shortage",1))),by)