IFERROR/INDEX Formula for Item Distribution Between Branches

Hi,

Please Login or Register  to view this content.

With the help of Marcol, an amazing guy from this forum, I prepared a template for stock distribution between our store branches using the formula above. I get the barcodes of products sold and requested for replenishment for each branch and pull a stock list. The formula returns the name of the branch with highest stock, and then I request 1(2-3-4) of that given item transferred away from that branch and to the one that requested. Branches(columns) are sorted left-to-right and ascending with respect to sales, so the formula returns the branch with lowest sales among the ones that carry the same quantity of stock. You can see an example of stock list and formulae used in the attached Excel file.

The formula works perfectly until a single product is requested by two different branches. When that happens, the formula will return that both products should be transferred away from the branch with highest stock. When the highest stock quantity is 2 and plenty of branches carry 2 (they both get transferred from one branch and others are left untouched), or when 4 different branches request an item and highest stock returns 3, that becomes a problem.

What I need is a tweak in the formula such that if a barcode repeats, the formula will return a different branch name each time. Starts from the branch with highest stock, moves on to the one with second highest stock for the second row, and third etc.. In the case of max.quantity being 2 and more than one branch carrying 2 stock, it should just move on to the next branch with 2 stock in the list. Basically when picking the same item twice, I want to be able to pick two different locations.

In the example, for Item1, Row #3 should return Branch 5 instead of repeating Branch10. If Item1 repeats once again, it should return Branch7.

Hope I made enough sense Is such a tweak possible? I work with 150 branches and 20-30k items weekly. I am able to change the template of the Excel file, so feel free to play around if need be

Any help would be much appreciated.

Thanks a lot in advance!


Transfer Problem.xlsx

That's absolutely amazing. Thank you so so so much!