• 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.

Batch Stock Allocation Formula

Hi,

I have a situation to allocate Batch Stock against certain Outstanding Sales Orders. To further explain,
Sheet-1: It will have sales order wise, Material wise Quantities for which orders have been placed.
Sheet-2: It will have material wise, Batch wise Stock available.

Requirement: I'm looking for some formula which will help me in plotting the Batch against the Outstanding Sales order. The batch plotted shall not exceed it's qty availability in Sheet-2.

For better understanding I have attached one sample excel sheet.

Excel formula or VBA Solution will help.
 

Attachments

vletm

Excel Ninja
Ganesh Vankadaru
Few hints:
If You would have replies, then after few days,
You should reread Your writings and answer for Yourself - Is it clear?
As well as You should able to add there Your expected result based Your given sample data.
You should able to do eg manually.
 
Hi Vletm,

Adding a small example for reference.

Input Report- 1: Stock Report

Mat Batch Qty
123 A 10
123 B 3

Input Report-2: Order Listing

SO Mat Qty
ABC 123 4
DEF 123 6
GHI 123 3

Output Report
Sales Mat Qty Batch
ABC 123 4 A
DEF 123 6 A
GHI 123 3 B
 

Attachments

Hi Vletm,

Adding a small example for reference.

Input Report- 1: Stock Report

Mat Batch Qty
123 A 10
123 B 3

Input Report-2: Order Listing

SO Mat Qty
ABC 123 4
DEF 123 6
GHI 123 3

Output Report
Sales Mat Qty Batch
ABC 123 4 A
DEF 123 6 A
GHI 123 3 B
You may refer excel as well. Have plotted manually for 1 Material
 
Top