Vehicle Routing With Two Dimensional Loading Constraints

[Kariczka]

Hello,

My name is Kate, and I am here with a problem of whenever or not I should use VBA or ordinary formulas in Excel in order to do Vehicle Routing With Two-Dimensional Loading Constraints case. What I need is when I have coordinates of four bottom-right corners:

X Y
24 0
30 0
10 24
8 38

Even if you are not familiar with 2L-CVRP you may still help as what I want to know is: In reality I have set of 200 coordinates (x,y) and I need to search for lowest y (its (24,0) and (30,0)) then I choose one with maximum x ((30,0) in this case). Then if for some reason item cannot be packed there, then I shall go to next lowest y, which is 24 giving coordinate (10,24), if I cannot use this corner, I search for next lowest y with as high x as possible, etc. BTW I use Evolver in order to find satisfying solution, I do not know if that helps.

Help is highly appreciated as I am not able to move forward with this problem.

[hoyasaxa215]

Kate, not entirely clear on what you're attempting to accomplish. Ideally, what would you like to be able to do?

[Kariczka]

If I have a large set of coordinates (x,y) I want to make a formula that is telling me what (x,y) I should choose. One should have as low Y as possible and if there are few coordinates with same Y then choose one with the highest X. If that fails for a reason of not having enough space for an item then move to next New lowest Y with highest X value. To make it clear I have a vehicle with width of 2.5m and height of 5 metres. The constraint about whenever or not the location of new item is possible depends on if there is enough space for it. Considering boundaries of four functions for each item and vehicle. I am sorry if I don't make myself clear enough. Coordinates are actually coordinates of bottom-left corner of the items put in the vehicle starting from putting first item's bottom left corner in (0,0). My priority is to choose corner with coordinate Y as low possible. I consider only bottom-left corner that must touch walls of vehicle or items orthogonally. Hope it helps. I really want to know how to deal with such a large data by using formula in Excel without using VBA. Thank you.

[hoyasaxa215]

Well, without uploading a sample file showing the data you're dealing with, I would just separate your x- and y-coordinates into two separate columns (e.g., Column 1 and Column 2). In Column 3, I would just sum up the x and y values. You could then sort the columns by lowest y value. Column 3 will allow you to quickly break any ties between coordinates with identical y values.

If this is unclear or not what you're trying to accomplish, please respond (ideally with a sample file to better illustrate your request).