Results 1 to 3 of 3

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

  1. #1
    Neophyte sunny281199's Avatar
    Join Date
    Dec 2020
    Posts
    2
    Articles
    0
    Excel Version
    office365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 to 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 required 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


    Basically, I've used v-lookup to index match Item Number (columnA) and Store onhand (columnF) qty from a separate spreadsheet.

    The Store on hand qty is the sum of all Part A found in other spreadsheet.
    That is why all rows has the same exact number for (Store onhand) (columnF)


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

    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 (columnI)

    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
    Attached Files Attached Files

  2. #2
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    13
    Articles
    0
    Excel Version
    2019
    Instead of trying to allocate each sngle row, why don't you try to do it with the cum order qty?

  3. #3
    Neophyte sunny281199's Avatar
    Join Date
    Dec 2020
    Posts
    2
    Articles
    0
    Excel Version
    office365
    Quote Originally Posted by RET View Post
    Instead of trying to allocate each sngle row, why don't you try to do it with the cum order qty?
    Hi RET,

    Thanks for response.
    Due to each part item number has a different due ship date, so its a nature of business to allocate the available quantity on hand by row


    rgds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •