Auto allocation, append next row if not able to allocate quantity

[sunny281199]

Hi all and good day,

I'm currently doing supply and demand fulfillment allocation.
I'm having issue with the store on hand quantity distribution with customer order quantity.
I need help on the formula smart enough to skip to next row if the store on hand quantity not able to fulfill order quantity
In short,
If the store on hand quantity balance still able to allocate, allocate it
If not able to allocate, move to next row

I have performed auto sorting based on customer priority, ship month and dates hence (refer to the attachment), the row sequence are not allowed to change.

Appreciated fellow forumers help on this issue

Column G

=SUMIF(A2:A$2,A2,D2:D$2)

Column H

=IF(IF(G2>F2,F2-SUMIF(A2:A$2,A2,D2:D$2),D2)<=0,0,IF(G2>F2,F2-SUMIF(A2:A$2,A2,D2:D$2),D2))

Column I

=D2-H2

Column J

=SUMIF(A2:A$2,A2,H2:H$2)

 

[sunny281199]

Hey guys,

Basically, I've used v-lookup to index match Item Number (columnA) and Store onhand (columnF) qty in separate Spreadsheet-B.
The Store on hand qty is the sum of all Part A found in Spreadsheet-B.
That is why all rows has the same exact number for (Store onhand)


So now, I would like to deduct the Order Qty (columnD) with Store onhand (columnF), provided it has enough to allocate and distribute.

If referring to row 9 and 10 (red font), the Order qty are exceeding the Store balance (columnJ) qty, thus it shall ignore it and placing it under Outstanding balance

Hence, the available balance qty Store onhand shall try to allocate for the next row 11 and 12 (hilite in yellow), which the balance still enough to fulfill Order qty.


My formula in (columnH) not able to tackle it especially cell H11,H12 & H20 where the Store bal able to supply for Order Qty


Hope this explain well and good for you and fellow forumers understanding.

Thanks