Distribution planning based on order data

Ok, sorry to all if I've put this in the wrong forum (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, it's kind of incidental. 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!!

Originally Posted by optical

Ok, sorry to all if I've put this in the wrong forum (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, it's kind of incidental. 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!!

What does the RouteCount refer to? Is that the number of orders for a postcode within the Route? I'm trying to understand what the 1,2 & 3 means for Route 18, and the 1 & 2 for Route 19.

Is the ultimate intention to add the Route, RouteCount and PCDriveTime data in columns to the right of each order by looking up the post code in the Route/PostCode table?

Where does the PCDriveTime come from and why does the drive time for route 18 contain 27, 5, & 16 (presumably minutes)? Is this a result of not all postcodes being driven to for a specific route?

Rgds

Firstly, apologies, my bad for poor explanation. Secondly, thank you very much for taking the time to read, and the effort to reply to, my post.
The output I'm aiming for consists of a matrix which lists the route number and its associated postcodes. RouteCount is simply the order in which each of these are processed within a route. The list I constructed was purely illustrative. So the idea is that I have e.g. route 18, and the first postcode takes 27 mins to drive, the second 5 mins, the third 16 mins. For my example the third PC is also the last for that route, after which we move on to route 19, the first PC of which takes 5 mins, the second 38 mins. This continues until all deliveries are complete.

The major problem isn't so much constructing the table, it's trying to do it in a way that's realistic - like I said, I'm struggling to visualise a way of writing code such that I cover all the postcodes UP TO a destination, without creating duplicates or just processing the destination without the intermediates.

Hopefully that clears up the issue somewhat; thanks again for the response, and sorry if I've not explained myself well - got 72 pints of beer in my folks' garage to deal with

Originally Posted by optical

Firstly, apologies, my bad for poor explanation. Secondly, thank you very much for taking the time to read, and the effort to reply to, my post.
The output I'm aiming for consists of a matrix which lists the route number and its associated postcodes. RouteCount is simply the order in which each of these are processed within a route. The list I constructed was purely illustrative. So the idea is that I have e.g. route 18, and the first postcode takes 27 mins to drive, the second 5 mins, the third 16 mins. For my example the third PC is also the last for that route, after which we move on to route 19, the first PC of which takes 5 mins, the second 38 mins. This continues until all deliveries are complete.

The major problem isn't so much constructing the table, it's trying to do it in a way that's realistic - like I said, I'm struggling to visualise a way of writing code such that I cover all the postcodes UP TO a destination, without creating duplicates or just processing the destination without the intermediates.

Hopefully that clears up the issue somewhat; thanks again for the response, and sorry if I've not explained myself well - got 72 pints of beer in my folks' garage to deal with

Could you zip up an example workbook and attach it here, showing the original order tables and post code/route data matrix you use, explain the process and decisions you go through in constructing the result table, and finally what the result table looks like.

Some of this may simply be a repetition of what you've already explained, but I find it's easier to answer the problem if it's laid out in an actual workbook containing the real data, that I can play around with.

Rgds

Thanks for the reply; I was kind of aware that I wasn't explaining it too well Problem was that I didn't want to scare off contributions by having people go "Simul8? What the hell is that?!"

The table you've mentioned is basically what my modelling environment 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'.

I've tried to post the data file, but even after trimming it down to an illustrative 1.5 day's worth I can't get it under 300Kb (ie over the attatchment limit). It'd be great if you could look at it - PM me and we can arrange an email. All I've done so far is reduced the individual postcodes to summaries for each day (sheet 2), so for example rather than a PC ordering 1,1,2 in separate rows, I've displayed it as a single order of 4. The route list (3rd sheet) is empty, but I've put in the titles so you get the idea. The 4th sheet shows an example of the output I'm looking for.

Great work deciphering my shoddy explanation, thanks again.