Batch Stock Allocation Formula

[Ganesh Vankadaru]

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.

 

[Ganesh Vankadaru]

Can anyone please help here.

 

[vletm]

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.

 

[Ganesh Vankadaru]

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

 

[Ganesh Vankadaru]

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

 

[AlanSidman]

=INDEX(Stock!$A$2:$A$32,MATCH(Orders!B3,Stock!$B$2:$B$32,0))