Allocating Inventory to country

Question

I am having two source files One is having Inventory for that particular code and other is having location. So I want to allocate the inventory to different locations based on their needs. I want to take running total of "Stock available " column and allot the nearest value to a location in "Approved " column. Also I want that no value from the stock available should be repeated.

Source 1

Source 2 is attached file

Output should be the highlighted(Approved) column

SOURCE2.xlsx

Dhananjay_Galphat1 · Answer

Thanks for reply.

The allocation should be close to total needed

the switz is getting that allocation based on Total needed.

and we are using the running total on stock available. so first 5 batch are allocated to India. next 32 to China and next 10 to Switz.

we cannot allocate the same as needed as the value depends on the values corresponding to the batch. the allocated value should be as close as possible.

Let me know if you need more clarifications

Luke_C · Answer

Hi @Dhananjay_Galphat1

Can you say more about what the 'Batch' column represents in your data? Also what logic is driving India and China to be allocated more than they need? And why is Switz getting less? The total in the second file is over 600,000 so I'd think everyone should get what they need?