Selkie on 24/3/2008 at 18:41
Ok, sorry to all if this is inappropriate for CommChat (mods please move if it'd be better elsewhere), but I'm in need of a little bit of programming/data sorting help cos I'm hopeless when it comes to that sort of thing.
And my Masters kind of depends on it :(
Basically, I'm modelling a truck-based distribution network in Simul8, which is a discrete event environment. Don't worry if you've never heard of it, this is more of an Excel problem. I've got a load of (Excel) order data which shows orders (just a number) from various postcodes, sorted by date; for example:
Date PostCode Qty
03/05/05 BD 2
The various postcodes are sorted into about 20 circular routes, so that each route has a fixed progression of postcodes which it drives. Since I want to be able to easily change the routes, I've come up with a matrix which contains postcodes in the correct order, organised into the routes, e.g.
Rte/PC 1 2 3 4 5 6
18 BD PQ AB WFT GG XY
19 GF BI ASL ET QP ETC
The problem is, of course, that not every postcode orders every day. But if one does it must be supplied which means the intermediate postcodes must be driven. So what I'm trying to do is basically thin down my order data into a realistic 'journey planner', based on the routes, which will tell my Simul8 stuff which postcodes to process. The idea is that it'll run through each day's orders, look at the routes, and assign each order to the appropriate route. Ultimately, I want it to come up with a table showing the best way of fulfilling that day's orders with the minimum of postcode processing. Here's an example of the format:
Route RouteCount PCDriveTime
18 1 27
18 2 5
18 3 16
19 1 5
19 2 38
OK, now I know that's a lot of stuff to absorb, but basically I'm asking if anyone has any suggestions for macros or visual logic or knows any Excel tricks to do this. The tricky part here is that I want to be able to change the routes - otherwise I could just do it manually :( Thanks in advance, any suggestions would be most helpful!!
fett on 25/3/2008 at 02:19
Jesus fuck I'm an idiot. :(
TTK12G3 on 25/3/2008 at 02:27
If anyone who helps Selkie helps me as well. I want to see the solution to this.
TBE on 25/3/2008 at 02:45
Quote Posted by fett
Jesus fuck I'm an idiot. :(
You're just now figuring this out? ;)
aguywhoplaysthief on 25/3/2008 at 06:39
The problem is that you've done everything in the wrong color. I'd go for a triadic color scheme - that should solve the problem.
Scots Taffer on 25/3/2008 at 07:17
I've done a postgrad in Ops Research, utilising programs such as Simul8 and I still don't have a fucking clue what he's blathering on about.
Chade on 25/3/2008 at 11:24
Ok, I'm no expert in OR, and I'm not entirely sure what you're asking, but it looks to me like this is (or should be) some sort of traveling salesman problem.
Assuming this is true, my question is: why predefine a small set of allowable routes? What is the advantage in that?
Ziemanskye on 25/3/2008 at 11:29
A more easily understood explanation of what you're doing might help.
What I think you have is -
orders: 03/03/05 BD 2
Which seems to be that on that date, there were two orders in the "BD" postcode. That's the easy one, but you probably have a lot of these.
Then you've got your routes:
18 BD PQ AB WFT GG XY
So that's route 18 - by default, drives round BD, PQ, AB, WFT, GG, XY. Right?
Now, this third one, that's not making any sense to me.
18 1 27
18 2 5
18 3 16
So, route 18 has one order to deliver and takes 27 minutes to drive?
Or is it that along route 18, it takes 27 DriveTimeUnits to reach postcode 1 - which would be BD in the above list, then 5 time to reach PQ from BD, then 16 time to reach AB from PQ?
All that sorted, what you're trying to do is...? My guess is - Automatically generate the most efficient (looping?) routes to take into account all of the postcodes that have orders on any given date, right?
Or am I making that too complicated - and what you have is fixed routes and all you're tying to do is assign each postcode's worth of orders to the correct route? That's a rather different problem, but is largely a question of how much your routes overlap - if each postcode is served only by a single route then it's a moot arguement, the order goes round the route through that postcode, otherwise it goes through whichever route reaches that postcode first. For which you just need an extra set of information totalling the time from base for each postcode along each route
18 1(BD) 27
18 2(BD, PQ) 32
18 3(BD, PQ, AB) 48
If route 18 takes 48 to get to AB, but route 6 takes only 9 to get to AB, then the orders go on route 6, and route 18 is just passing through it because it can't teleport through postcodes it's not delivering to.
Since you've said you want to be able to change the routes, the calculations for how long each route takes from base to said postcode would need to be automated from your route-lists, but otherwise, one or the other of us isn't looking at the problem right. (Probably me: I've more of a AI/path-finding background)
I mean, I can't help you with the excel/macro stuff, but since there's folks here who seem interested, being able to actually understand the problem is probably all that's stopping folks who do know what you need on that front from cluing you in.
Selkie on 25/3/2008 at 13:49
Thanks for the reply; I was kind of aware that I wasn't explaining it too well :erg: Problem was that I didn't want to scare off contributions by having people go "Simul8? What the fuck is that?!"
Great work deciphering my shoddy explanation, thanks again:D The table you've pointed out is basically what my modelling environment (Simul8, you know it, you love it Scots) calls a Job Matrix. It tells a workcentre in what order to perform what tasks. The middle count, the 1,2,3... is simply the order in which it processes the times (PC's) of that route. So yeah, you were right on, my example from before is basically saying:
"For route 18, work for 27 mins, then 5 mins, then 16 mins. Having done that, move on to route 19, and work for 5 mins then 38 mins..." etc etc
So the aim of the game is to organise a day's deliveries into the most efficient Jobs Matrix, given that I want to be able to change the routes. I'm basically trying to see what effect altering the routes has on the timeliness or whatever of a given set of orders. The part I'm having trouble with is coming up with the code that will go "Right, got these orders, with these routes, here's the best way of fulfilling them".
OK, now having hopefully explained that bit, I'll move back to confusing you ;) What I was thinking of was moving through a route's list of postcodes and saying temporarily "add this postcode to the Jobs Matrix" regardless of the presence of an order. If it gets to the end of a route and there's been no orders, that route is removed and vanishes. If it gets to the end and there was an order somewhere, it eliminates all postcodes after that order (in effect saying 'go this far but no further'). To handle the return journey, I'll just sum all the times of the outbound and run that as a single 'Postcode'.
Thief13x on 25/3/2008 at 14:12
just throwin this out there...does is it a derivation of the Traveling salesman problem? (
http://en.wikipedia.org/wiki/Travelling_salesman_problem)
Disclaimer that I havn't read your entire post, I have to leave for class in 4 minutes but if you need help with that i've programmed it
