Thread: I need help building the structure of this program.

First of all I want to inform you that I don't really have any experience in excel programming, this is actually the first program i plane to make. of course with your help :D
but I have some experience in using excel sheets and VB.net, so I pretty much understand the concept of it, the main issue I have is figuring out the commands I have to use to apply a certain function in my mid.

to the main subject, I basically need someone to help me convert the idea that I have in my mind into codes and commands in excel. I would reallllllllly appreciated any kind of help I can get.

Here is the idea:

Lets say that I have 18 petrol stations. each station have a certain code number. each of them also have a certain quality of petrol in their tanks. we also have 13 heavy vehicles to transport the petrol to these stations. our job is to arrange a schedule for these 13 vehicles to transport the petrol to the 18 stations.
Each vehicle have 4 compartments or less. each compartment can take up to few thousands of gallons. as an example, the vehicle (TF305) have 3 compartments (1,500 - 1,500 - 2,000), so the (TF305) can carry a total of 5,000 gallons per trip.
Each vehicle can take up to 2 trips max.
Each vehicle have a certain driver we assign manually.
The vehicle HAVE to empty all of their compartments the moment we set it off. It CANNOT return to us with any petrol in it.
Each compartment of a vehicle should be emptied in one station only. for example, if I sent the (TF305), total 5,000 gallons, to station (769) which needs only 3,000 gallons. Its going to have to empty the (1,500 - 1,500) compartments in the station. and leave the station with only the 3rd compartment (2,000) to take it to another station. in this situation the (TF305) cannot give the station (769) in the following order (1,000 - 1,000 - 1,000) and leave the station with (500 - 500 - 1,000).
Each station can receive as many vehicles as necessarily in order to reach the right quantity of petrol that it need, we determine the amount it needs manually. for an example, I want to send 3,000 gallons to station (769). I can send the (TF305) to the station (769) and empty the compartment (2,000), then we send another vehicle (TF304), with compartments (1,000 - 1,000 - 1,000 - 1,000), to empty one compartment (1,000) in the same station. but both vehicles HAVE to go to another station/s to empty their other compartments.

That's pretty much all what I want to do. I attached an excel file that contains both the stations, the vehicles and the quantity we want to send under "QTY/SUP". I want the button in the middle of the schedules to do all the arrangements necessary in order to fill the columns "Trip 1" and "Trip 2" under "Vehicle", and the "Driver" column.

I would really really really appreciate any sort of help
Thanks a lot.

What you got is a typical LP problem and the Excel Solver could be a possible tool for this job. The question is if it’s powerful enough.

To set up a model of this kind you do need a number of constraints. Some you already mentioned but you also would need the distance between each station. If a vehicle can’t unload all its cargo to a station which station should it go to. You do want to keep mileage to a minimum and spend as little time as possible filling all stations.

Do you also have a number of different grades that must be separated in the trucks? Models like this soon ends up quite complex as one need to take in account a number of parameters.

I would start reading about LP planning and distribution. I would guess there are a number of commercial programs you can buy GRTMPS by Haverly is one and Aspen Petroleum also sells this kind of program. Still they are quite expensive and much more powerful than you need but one can always call and ask for advice.

You could also contact the makers of Solver http://www.solver.com/ and ask if the have any distribution models.

Alf

Thank you Alf for you concern and help, I downloaded Excel Solver and watch few videos and read some examples, but I cant seem to be able to use it -.-"
could you please explain to me more on how to solve the problem on Excel Solver ?
thanks a lot.

Thank you for pointing me to these useful examples.

its true that "Knapsack" seems somehow close to what i want. but in my situation, I don't have an option to not send these certain amounts of gas to the stations. also, there is no loss at all. my task is so clear, I have to supply a certain amount of gas to a station, I determined the amount for each station. I just want excel to arrange my tracks for me in a certain way that it will satisfy the constrains I mentioned above in the tread.

so how will I manage to do that ?
thanks

the Excel Solver could be a possible tool for this job. The question is if it’s powerful enough

Combining 18 stations and 13 trucks gives me a matrix of 234 cells to change. The maximum number of changing cells for Solver in Excel 2003 and 2007 is 200. I don't know the limit for Solver in Excel 2010. If it’s the same then Solver won’t do.

For model building you are on your own. Sorry I can't help you there. My best advice is Google for appropriate books on the subject.

Alf

i still need a solution ......