Spread an excess

[Pierre]

Hello!

Long time I didn't post here!

I have a file in which shops declared a quantity of products which can be different from the quantity really received. Some shops have an excess, some have a deficit.

What I would like to do, is use the quantity in excess and allocate to the shops in deficit.

The allocation should be:
1) allocate a quantity to the shop(s) with smallest % reimbursement untill it reaches the % reimbursement of the 2nd shop with smallest reimbursement
2) allocate the remaining qty to these 2 shops until the % reimbursement reaches the % reimbursement of the 3rd shop, etc...
3) Repeat until there is no excess qty to allocate

I was thinking to use a solver to do but I am not sure how to declare the constraints....

Any help would me more than welcome!

 

[vletm]

Pierre
Interesting terms for me ...
but I tried something.
Press [Do It] and ...
You'll get some values.
Ps. I would like to know how much those shop really has needed/used and after that solve what to do ... hmm?

 

[Pierre]

Thanks vltem!
I tried to understand the code but it's quite complex

The answer given by the macro is slightly different that what it should be, for shop 1932 for exampleit deduces 39 while it should deduce 38 and total units to add should be 59.

Anyway the result should be close enough for me

 

[vletm]

Pierre
You didn't give 'wanted result' and
as I wrote ... or tried to write ... I would solve this another way.

 

[vletm]

Pierre
What is complex and what isn't ...
You would reread Your 'The allocation should be: ...', that's complex!
I add one more feature and You can see how this works!
Check instructions from file.

 

[Pierre]

Thanks a lot vletm!