Stock allocation

Experts,

I need your help to solve my problem.
Qty fulfillment already been defined, but the problem is the stock at different locations,
and I need it to be mapped from the WH to the stores based on store rank priority.


Thanks

I have updated attachment above

Where and which range is your expected result?

on example file, the result is at range A12:D23

@halimgunawan
What is the meaning of "priority"? Does it mean that stores with larger rank values obtain needed stock before those with smaller rank values?
Anyway, what I can see now, it looks to me like an optimization problem which can be solved by using Solver.
The decision variables would be numbers of a particular stock allocated to the specific store (5 stock types *10 stores =50 variables). To take priorities into account the objective function should be a sumproduct of numbers of stock items and ranks of stores. The constraints should ensure not exceeding the stock amounts and fulfilling the needs of stores as much as possible.

One remark. Your example solution describes allocation not only a particular stock to stores, but also a particular stock from a particular warehouse to stores. However, without extra parameters which differ in any way one WH from another, such detailed allocation is basically impossible to be done in a unique way.
Let's consider K-123. The stores need 11 pieces of K-123 altogether and the warehouses have 8 pieces of K-123 altogether. The warehouses send all what they have of this stock, but particular allocations "WH to store" are not unique.
Your example is
K-123 WH1 Store 1 2
K-123 WH1 Store 2 2
K-123 WH2 Store 2 1
K-123 WH2 Store 6 2
K-123 WH3 Store 6 1
But for example an allocation plan
K-123 WH1 Store 1 2
K-123 WH2 Store 2 2
K-123 WH2 Store 2 1
K-123 WH1 Store 6 2
K-123 WH3 Store 6 1
is different from yours but equivalent in the sense of how your problem is specified.

Later today I'll try to prepare an file with a Solver optimization model.

OK, so try this.
If fact, as you notced, priorities for stores really matter only in case when the total needs of all the stores for a specific stock exceed the total availability of that stock in all the warehouses. Otherwise, you can supply the stores with all the stock items they need anyway.

What do you mean by "expected result"?
The format of output data
stock code - WH number - store number -quantity
or an exact solution?

About an exact solution, I mean in your solution K-123 is allocated
Store 1 2
Store 2 2+1 = 3
Store 6 2+1 = 3
whereas in mine
Store 2 2
Store 4 1
Store 5 2
Store 6 3

Is it something wrong about priorities?
I calculated the "priority performance" for deliveries of K-123 to stores (qty multiplied by rank) as 2*2 + 1*4 + 2*5 + 3*3=27.
In your solution it is 2*1+3*2+3*3=17.

Seen it several time and waiting for solution from other experts...
Anyway, it is so complicated because it required:
Each row down from D13:
1) To update the stock of current code in each Warehouse
2) To determind which store to feed, base on rank level and either WH capacity or store needs

I believe it can not be solved with one single formula.

Do you mind using some helper columns (or tables probably) some where in the worksheet?

For each row in input field, i.e, row 13, I try to calculate beginning and ending stock of each warehouse (J13:O13) and the remain of each store requirement (Q13:Z13) for code in A13
If you want to hide these tables, move it in to column AA than hide the column.

In fact, it is too complicated to explain in words! And my English is so bad. Sorry about that.
Basically for each row, try to record the warehouse stock, the stock requirement... in helper tables
I am not doubt that it could be solve with VBA in professional way...

anyway bebo021999, if I change the "qty to fulfill" into different numbers, why the result become N/A ?

Post the worksheet again with the error to see what happenned

Perhaps the following will help.
I added two helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper displays the total Qty to fill for each row using: =SUM(Q3:Z3)
The second displays the number of rows to increment using: =IF(A4="",1,AGGREGATE(15,6,(ROW(A4:A$7)-ROW(A3))/(AA4:AA$7>0),1))
The formula in A14:A23 has been modified to read:

Formula:

Please Login or Register  to view this content.

Formulas in B14:D14 and down have been prepended using: =IF(A14="","",...
Let us know if you have any questions.

Paste the following into cell A13:

Formula:

Please Login or Register  to view this content.

Note this formula is for cell A13 only.
Let us know if you have any questions.

I believe that at least part of the problem is that in the real data there are codes that do not have stock in any of the 5 warehouses.
Make the following modification to cell BC3 and down: =MIN(SUM(B3:F3),SUM(AF3:BB3))
Let us know if you have any more problems and/or questions.

You're Welcome. Remember that bebo did most of the work. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Try pasting the following in cell BE3 and then copying down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions/problems.