• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stock allocation to outstanding order balance

SUNCIOUS

New Member
Hi all,

I need some help with my outstanding order report.
Inside my excel sheet, I need a formula to allocate available incoming stock from my inventory to specific order lines, allocating the stock based on the Order No.
The reason for requesting specific Order No is due to item like "Screw" is common part used in different customer's models which is represented by the Order No. followed by Model A or B. If no stock, the result would be 0.

However, there is also another problem as Order Line is not available under Sheet 2 and there may be a few lines under the same Order ID with the same item, only with different request delivery dates.

Thanks in advance!

Example:

Sheet 1-
A1 B1 C1 D1 E1 F1
Order No Order Line Req del date Item Order Qty Result
1001/A 1 15-May-20 Screw 20 20
1001/A 2 01-Jun-20 Screw 50 50
1001/A 3 05-Jun-20 Screw 10 0
1002/A 1 15-Jun-20 Resistor 10 5
1003/A 1 01-Jul-20 Capacitor 5 0
1004/B 1 15-Jul-20 LED 3 3
1005/A 1 05-Jun-20 Resistor 6 6
1006/B 1 05-Jun-20 Screw 4 2

Sheet 2 -
A1 B1 C1
Order No Part Incoming Qty
1001/A Screw 20
1002/A Resistor 5
1004/A LED 3
1005/A Resistor 6
1001/A Screw 50
1006/B Screw 2
 

bosco_yip

Excel Ninja
Stock allocation - outstanding balance

In H2, formula copied down ;

=MIN(G2,SUMIFS(Sheet2!D:D,Sheet2!A:A,A2,Sheet2!B:B,D2)-SUMIFS(H$1:H1,A$1:A1,A2,D$1:D1,D2))

67919
 
Last edited:
Top