Equal allocation of stock variations

Hi,

Looking for some help with a growing task. I have 35 market areas which need to receive allocation of stock. This stock can vary in specification and it's important that they each receive an even split. At the moment I'm using tables and formulas to divvy the stock up but the number of variations within the stock is growing making it a very manual task due to rounding errors. I've attached a sample of data for 10 market areas and 93 stock items, of which there are 18 variations. What I would like to return is a Market Area against each order number with an even distribution of models for each area. Any thoughts on how this could be achieved with code? I've used a small sample to illustrate but the actual data would be 35 market areas and up to 3,000 stock items with 100+ possible variations.

Any help would be greatly appreciated. Many thanks in advance.

Book1.xlsx

Originally Posted by pjwhitfield

what do the values in column B mean? are these stock amounts ie should MA01 get 16/93rds of the stock while MA04 gets 33/93rds?

These are the volumes that each Market Area should receive so yes, you're right. Sometimes the volume is 0 so would need the code to cope with that. The total volume should always match the number of stock units though.

Each MA can only receive whole numbers so where MA01 is due 17.2% of each variations they would automatically receive the rounded down volume of each variation then the stock left over is currently manually balanced out. MA01 might receive higher than 17.2% of one model and less than 17.2% of another. MA03 would likely receive 1 of the variation with the highest occurrence which in the example would be one of the units from row 36 to 76 of which there are 41.