+ Reply to Thread
Results 1 to 14 of 14

Algorithm for arranging cars in warehouses

  1. #1
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    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!!!!
    Attached Files Attached Files
    Last edited by eeeo; 07-30-2020 at 09:34 PM.

  2. #2
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    Help Please

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: Algorithm for arranging cars in warehouses

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Algorithm for arranging cars in warehouses

    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?

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,584

    Re: Algorithm for arranging cars in warehouses

    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
    Attached Files Attached Files
    Last edited by Alf; 07-30-2020 at 01:51 PM.

  6. #6
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    Hi guys Thanks for help.
    but it needs to be without solver
    because i need to use it for other program that look like excel (have the same function but no solver)

  7. #7
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    Ok thanks AliGW i will try to explain in more detail!!!

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,554

    Re: Algorithm for arranging cars in warehouses

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    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
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    Hi Mr Shorty
    First of all thanks so much guys for all the help

    My Program look like excel and i have as many spreadsheets as i want.
    I can work with php inside this spreadsheets
    I don't have built-in Solver type library or similar libraries
    i dont know if i can import php solver maybe i don't know.
    it's not optimize its just arrange (i update the excel and added another example)

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Algorithm for arranging cars in warehouses

    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...)

  12. #12
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    bebo021999
    Yes thanks!!!

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Algorithm for arranging cars in warehouses

    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
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-25-2017
    Location
    London, England
    MS-Off Ver
    OFFICE 2016
    Posts
    15

    Re: Algorithm for arranging cars in warehouses

    Thank you very much bebo021999
    wow you are awesome

    Amazing forum

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 01-02-2019, 04:55 AM
  2. Replies: 0
    Last Post: 08-30-2017, 01:53 PM
  3. Excel algorithm for equalizing item usage like cars & printers
    By b2netman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2017, 12:59 PM
  4. Unique Accounts for Warehouses
    By annara in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2016, 01:05 PM
  5. Moving Stock between Warehouses
    By jarrellanthony in forum Excel General
    Replies: 6
    Last Post: 05-22-2014, 05:36 PM
  6. Problem with warehouses location and distances
    By fernando112 in forum Excel General
    Replies: 6
    Last Post: 03-28-2014, 02:04 AM
  7. Items / Warehouses
    By ElmerS in forum Excel General
    Replies: 6
    Last Post: 02-13-2010, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1