Need assistance on coming up with an inventory allocation formula

Hi. I'm a newbie here. I've been searching online for awhile on assistance on coming up with an inventory allocation formula with no luck. I turned to this website as most of the search hits I've gotten are from here.

I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column. I figured there's should be a better/faster way of doing this via a formula.

The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:
1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.

Hope someone here can help. Thanks.Inventory Allocation.xlsx

Originally Posted by FDibbins

Hi and welcome to the forum

Not really sure what you want here, or what you would want the answer/s to look like, but this will pull the SoH based on the Item #...
=VLOOKUP(A2,Stock!$A$2:$B$2212,2,0)

I see that you have a number of the same Item, with the same Qty and the same Date (A3:A5 for instance)?

What would your results look like - and why?

Thanks for the feedback.

there will be instances where items, quantities and due dates are the same but these are for different projects (FYI, the column containing the project name I deleted to simplify the spreadsheet). I'm trying to allocate on-hand stock found on the 2nd tab to the different projects on the 1st tab.

this is a FIFO spreadsheet where on-hand stock needs to be distributed to the demand on the 1st tab. So, taking the 1st item (GT10027) as an example:

On-hand inventory is 29. the distribution would then look like this:

Item# Date Qty Allocated Stock
GT10027 8/14/14 6 6
GT10027 8/15/14 1 1
GT10027 8/15/14 1 1
GT10027 8/15/14 1 1
GT10027 8/15/14 2 2
GT10027 8/15/14 1 1
GT10027 8/15/14 2 2
GT10027 8/15/14 2 2
GT10027 8/15/14 2 2
GT10027 8/19/14 1 1
GT10027 8/21/14 3 3
GT10027 8/21/14 2 2
GT10027 8/22/14 3 3
GT10027 8/22/14 2 2
GT10027 8/22/14 4 0
GT10027 8/22/14 1 0
GT10027 8/22/14 2 0
GT10027 8/22/14 4 0
GT10027 8/25/14 2 0
GT10027 8/25/14 1 0
GT10027 8/25/14 2 0
GT10027 8/25/14 2 0
GT10027 8/26/14 1 0
GT10027 8/27/14 5 0
GT10027 8/29/14 2 0
GT10027 8/29/14 1 0
GT10027 8/29/14 1 0
GT10027 8/29/14 1 0
GT10027 8/29/14 2 0
GT10027 9/08/14 2 0
GT10027 9/29/14 1 0

Since there's only 29 units in stock, I'm only then able to distribute/allocate to rows A2 thru A15 as the total demand count, through A15 is 29 units. So, let's say row A15 had a demand qty of 4 units instead ( thereby increasing total demand qty from rows A2 to A15 to 31 units, I still can only allocate 2 units to it.

I would like to automate the allocation of on-hand stock to the demand. Once stock is depleted, succeeding rows of demand should then show 0. At this time, I'm literally doing it manually by highlighting the demand qty cells, for each item, until the sum matches up to on-hand stock. I then copy over the highlighted cells to the allocated stock column.

Hope this all makes sense.