How To Carry Out Complex Allocation Among Various Sites?

[xlmaniac]

Dear All,
The solution to this complex problem is beyond my capability and imagination but I know that this would be very simple to many excel gurus here in this forum.
Would be grateful , if somebody could suggest a solution & it will save unwanted manual intervention that presently I am carrying out.
I am trying to allocate non-performing SKUs across stores based on certain parameters. Stock with Positive Qty are the Source and that with Negative Qty are Destination.
The transfer can happen under 4 different methods for Same SKU:-
1)One Store(Source) To One Store(Destination).(1:1)
2) One Store(Source) To Multiple Store(Destination).(1:N)
3) Multiple Store(Source) To Multiple Store(Destination).(N:N)
4) Multiple Store(Source) To One Store(Destination).(N:1)
I would like to provide samples under each scenarios with fixed rules along with the desired result.
Scenario 1-(1:1):-
Under this scenario, against any given SKU, the qty from Source will be allocated to destination Store based on the MIN qty between them.
[TABLE="width: 172"]
<tbody>[TR]
[TD]Store[/TD]
[TD]SKU No[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]14[/TD]
[TD]-14[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]14[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]
In this scenario, 12 Pcs from Store F will be allocated to Store E.
Scenario 2-(1:N):-
[TABLE="width: 172"]
<tbody>[TR]
[TD]Store[/TD]
[TD]SKU No[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]12[/TD]
[TD]-34[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]12[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]12[/TD]
[TD]-12[/TD]
[/TR]
</tbody>[/TABLE]
In this scenario, the stocks from Source(Store-C) will be allocated to the store with highest ABSOLUTE VALUE basis at first(Store:-B,34 Pcs), followed by to (Store:- D,6 Pcs). This rule will be applied for all SKUs under this Scenario(1:N).
Scenario 3-(N:N):-
[TABLE="width: 303"]
<tbody>[TR]
[TD]Store[/TD]
[TD]SKU[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]16[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]16[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]16[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]16[/TD]
[TD]-3[/TD]
[/TR]
</tbody>[/TABLE]
This is a very complex scenario with many possible combinations possible.
One rule which needs to be applied here is that highest Positive qty stocks at first will be allocated among negative stocks. In case any residual qty still needs to be allocated it should, check the 2^nd highest Positive Qty Stocks and allocate. This loop will continue like this till the allocation is complete.
Scenario 4-(N:1):-
[TABLE="width: 297"]
<tbody>[TR]
[TD]Store[/TD]
[TD]SKU[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]22[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]22[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]22[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]22[/TD]
[TD]-10[/TD]
[/TR]
</tbody>[/TABLE]
In this scenario, the Highest Positive Qty needs to get allocated 1^st, followed by 2^nd Highest Positive Qty & will continue like this.
Sum of aggregated Positive Qty needs to be less than or equal to negative qty.
Finally I would like to populate the end result of the aforesaid 4 Scenarios, in the following format in a separate sheet.
[TABLE="width: 409"]
<tbody>[TR]
[TD]SKU No[/TD]
[TD]Source Store[/TD]
[TD]Destination Store[/TD]
[TD]Allocated Qty[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]F[/TD]
[TD]E[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]I[/TD]
[TD]G[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]H[/TD]
[TD]G[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]L[/TD]
[TD]N[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]K[/TD]
[TD]N[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Pls help with the possible solution.