Stock Allocation value

[isskint]

Good evening forum,

Been some time since I came to you for assistance, but this one has got me.

I am trying to figure out a formula to return the value of stock being allocated to an order.

I have 2 lists, Stock age and Backorder

Stock age lists receipt date, product code, serial number, quantity on serial, unit cost. This list is sorted by oldest receipt first
Backorder lists product code and quantity ordered. This list is sorted by oldest order first

The issue is some serial numbers may be a single unit or may be multiple units. So an order for 3 units may come from 3 seperate consecutive serials, 1 unit from one serial and 2 units from the next serial OR 2 units from one serial and 1 unit from the next serial. On top of that each serial may have different unit costs.

A little example/sample attached showing expected outcome for first 2 orders and how that would be achieved

[JeteMc]

This is an inelegant proposal which I hope that someone will improve upon:
1. Insert four rows at the top of the Stock Age sheet
2. Populate G1:L1 using: =SUM(G6:G44)=G4
3. Populate G2:L4 by pasting transpose columns A:C of the Backorder sheet
4. Populate G6:L44 using: =IF($C6<>G$3,0,MIN(G$4-SUM(G$5:G5),$D6-SUM($F6:F6)))
5. Populate the Stock Value column on the Backorder sheet using:

Please Login or Register  to view this content.

Let us know if you have any questions.

[isskint]

Hi JetMc, Thank you for taking the time to think on this.

As you say, an inelegant proposal, but hopefully it gets the little grey cells working. I had tried something similar (using Filter() to generate the 3 row list you put at the top to keep it dynamic) but with 40,000 records on Stock Age and 4,000 records on BackOrder, Excel gave me the middle finger!

Working on something now based on your approach and some voodoo from Leila Gharani!