Excess allocation

[Pierre]

Hello,
I have a file which helps me re-allocate quantities received to a warehouse from shops, in order to calculate the reimbursement of shops.
The way it works is that I look at the qty received vs qty declared by shops for each products, this will give me usually an excess, and then reallocate this excess 1 by 1 starting with shop with minimum reimbursement and adding 1qty each time in order in increase the % of reimbursement by shop. I can have more than 100% reimbursement.

The file works well but I want to add a condition: if, for a product, the type=E and difference = 0, then I don't want to add qty to this shop, and continue to allocate qty to other shops.

I don't really see how to do, if someone can help, it be quite appreciated

 

[NARAYANK991]

Hi ,

Please explain the entire requirement in terms of Excel worksheet cells / rows / columns.

Using English here and French within the worksheet is not helping either.

Narayan

 

[Pierre]

Hello,

Sorry, I'll try to explain it better.
I have a list of shop number (column A) and product (column B). For each product they return the shops declare a quantity they send back to the warehouse (column D, qty declared). At receipt at the warehouse, products are scanned and we have a Qty received (column E). This quantity can be different (in excess or minus) than the quantity declared by the shops (column F). The difference is calculated column G.

The goal of this file is to allocate the product which are received in excess (quantity received> quantity declared by the shop) in order to harmonize the percentage of reimbursement each shop will get. The idea is to look, for each product, the quantity received in excess in total, then we allocate this quantity (column I), unit by unit, looking at which shop has the smallesst reimbursement percentage (column K).

This is how it works now.

I would like to modify it a bit, so that if we arrive at one point for a given product, that all shops have a 100% reimbusement (column K), we continue to allocate the excess but only to shops who have a difference (column G) >0. We can have a reimbusement percentage for these shops above 100%

I hope it is clearer and thank you for your help

 

[Pierre]

I modified the formula in the last column so that when there is an excess the % of reimbursement is slightly more than 100%.

My idea would be then to add a 3rd "Do Until" loop that would do until excess=0, look at the 2nd smallesst reimbursement percentage, and add a unit to the column I, the same way the previous "Do Until" loop is working.

The main thing now is how to write this "look at the 2nd smallest number in a range that contains duplicates" in vba...?

I found a formula with large(if and array formula, but:
1) It doesnt seems to work when there is 2 values in the list (if I have 100;100,01;100 it will give 100 as result)
2) How to write it in vba language?