Algorithm for arranging cars in warehouses

Hello Guys.
First of all thanks so much guys for all the help

I have a little problem to find an Algorithm for arranging cars in warehouses,
I explain.
I have Four tables:
1. Table A that show max Capacity in every site(warehouse).
2. Table B shows the number of cars that we have for each car model (Quantity)
3. Table C Its the Priority Which model enters first and where.
4. Table D is the end result, in this table we have the Manual solution and the Algorithm(that need to be automatic).


Attached an excel file with an Example!

A small update
in all tables there is no way one table will be with one period and the other with different period
all tables will come with the same period.
There are three things that can change
Capacity, Quantity,Priority
i added another example in the excel


Thanks!!!!

233 thread views and 6 workbook views, but no help offered - this shows that there is something problematic with your query and/or sample data. Maybe you need to explain in more detail what you are trying to do and why.

It is quite easy to work with current data:
Single period
priority is increasing with car model is sorted in both tables

But, I support there are changes in : period, priority, car model position

Therefore, could you give more scenarios to get a general view?

It is possible to use the excel add-in solver but probably this is not the best solution. Still it works. Solver is not installed by default so check under the "Data" tab that you
have the solver icon. Click on this and as this worksheet contains a solver mode you just click button marked "Solve".

If Solver is not found you have to install it first.

To get the priorities right I've set different arbitrary values (Site A wt, Site B wt and so fort) and the site with the lowest
value i.e. 1 (first priority) get the highest value. The object function (what drives Solver) is set to achieve the maximum value with some constraints i.e. the number of cars should be integers.
equal to or less than the capacity for each site. The number of a particular brand of a cor should be equal the sum of that particular car in site A, B and C.
Cell C14 checks that the sum of cars is equal to or less than the total capacity.

As priority 1 has the highest value this will ensure that all cars with priority 1 will be parked first, then solver "parks" cars with priority 2 and so forth.

As your uploaded spread sheet is oriented with writing right to left I do hope excel converts if to you format since I use the left to right setup.

Alf

When you say that this is for more than just Excel, are you limiting this to spreadsheets, or are you looking for algorithms applicable to any programming language? And we are assuming that the other spreadsheets or programming languages do not have a built in Solver type library or similar libraries? Your sample file includes a manual solution, but it is not clear to me what we are trying to optimize or arrange or what exactly the algorithm is trying to do or what "scores" it is trying to balance? As beebo021999 notes, how representative is the example?

As far as the algorithm goes, the main question I have is trying to understand what we need to optimize -- what the objective function would be. These kinds of problems often reduce down to "knapsack" problems (https://en.wikipedia.org/wiki/Knapsack_problem ) which is NP-hard/NP-complete, which means that these problems often must be solved by a brute force "generate all possible combinations, compute objective functions/scores for each combination, then choose the best combination" kind of algorithm. If we can't assume that our spreadsheet or programming language will have a build in solver or library for these kinds of problems, then are we assuming that we must program the entire thing from scratch within the chosen programming language, or can we rely on any built in libraries?

Lots of questions. I think we need a better idea of what the problem statement really is.

Your WS is set with column A,B,C running from far right to left, so it is inconvernience to use VLOOKUP function. Is it possible to convert it to normal format? (Column A is the first left then, B,C...)

Working on case 2:
Table D is sorted with priority order. With Model sort, we can not calculate the remaning WH capacity row by row.
In T83 copy to V83

Please Login or Register  to view this content.

Placed:

Please Login or Register  to view this content.

Drag ann down