Hi All,
Hoping someone can help with formula I am trying to do to transfer figures from start count locations to multiple locations based on the end counts.
As there could be several ways to give the same result is this one to use with Excel Solver? I haven't used before, so finding difficult to figure the way to achieve result.
So from the table highlighted here with the blue box I have the start counts for each location and then after stock is moved around locations I have the end counts. So what I'd like to do is as these counts change it automatically fills the transfer amounts against the correct locations in table highlighted with orange box.
I thought if I use Excel Solver perhaps I could get it to enter the shortest route to give the least amount of transfers by using table created within green box. Then I'd use vlookup formula to enter the instructions into table highlighted here with orange.
Currently I have manually inputted result:
So instruction is to transfer:
2 from LOC Paint into LOC Final
1 from LOC Clean into LOC Paint
2 from LOC Clean to LOC Reject
As long as the total end counts are correct in each location it doesn't matter which start location it is taken from just as long as it uses as fewer transfer operations.
Is this possible or is there another way?
Many thanks