Jump to content
 

Generating Freight Trains with Excel


CameronL
 Share

Recommended Posts

1302515651_Plan_Sixteen_Scen_Bboards_Cont-Run_Heights_FreightSystem(002).jpg.22823333e4353ee3f5cdadebf86408c0.jpg

 

This is a track plan of the Nether Madder and Green Soudley railway. It’s not mine. It’s the RMWEB topic of Martin S-C of this parish. As regulars to the topic will know, I’m a keen follower (among many) and have occasionally added my ramblings to the discussion. It’s covered the development of a system layout from design through waterproofing the garage it has been built in, baseboard construction, track laying, electrics and some beautiful stock construction, painting and weathering. If you haven’t seen it, check it out.

 

One of the many issues it has touched upon is allocation of wagons to freight trains. Martin went into great detail about this, describing his use of a pack of cards and a 100-sided dice (role-playing games for the use of) to generate random consists of eight wagons. Into such great detail, in fact, that it got me thinking.

 

I’ve been toying for some time with the idea of using some kind of computer system, probably Excel, to do the job, and Martin’s  description of his needs was so comprehensive that it inspired me to have a go using his data and rules (my own being a long way from as well thought out as his).

The detail of it is here 

 

If you look at this page you’ll see the full details of Martin’s requirements. (You’ll also see some ramblings from me about special loads and random engine breakdowns).

 

But basically what Martin has is –

·        Twenty-one destinations, both stations and industries, which need to receive a variety of different wagon types.

·        Twenty-two (could soon be twenty-three) wagon types, some of which will only go to one destination, some just about everywhere.

·        Different “weightings” for both destinations and wagons i.e. larger destinations will receive more wagons than small ones, and some wagons need to be seen more than others. The layout is a coal-mover with a rich variety of other industries so coal will predominate.

 

And the Excel spreadsheet below is what I came up with to solve this problem. Have a go. I’ve done a nice, macro-enabled version with a “Reset” button but RMWEB won’t let me upload it so if you want to see it change just put the cursor in any blank cell on the “Train” page and press the “Delete” key on your keyboard.

 

If you want to know how it works there’s a Word document as well describing the principles. Please feel free to download both, have a go and if you have a similar problem adapt it to your own needs. I’ve put in some little extras on the “Other Goodies” sheet as well to handle other situations –

·        “Special loads” – the  loco boiler on a Lowmac, the new piece of heavy plant at the quarry etc. This let you run such loads without them being seen so often that the situation just becomes unbelievable.

·        Breakdowns – if you want to make life really interesting by having one of your roster break down and go off to be repaired, so you have to find a replacement for its duties.

·        Rare Occasions – anything else.

This whole system uses the abilities of Excel to generate random numbers then lookup information according to the results. What I’ve shown you works for the NM&GS. It can be adapted for anyone’s situation.

 

It does have certain advantages over any system based on pen and paper / playing cards / 100-sided dice / crystal balls / divination –

1.      It’s faster

2.      You don’t have to worry about your 100-sided dice ending up under the sofa / in your mug of tea / in your dog

3.      It works with any numbers you want to put in – you can’t buy 97-sided dice.

4.      It never produces an impossible value – for instance if your playing cards turn up a wagon type that only has two destinations out of twenty it might take a lot of rolls of the 100-sided dice before you get a valid destination. This system only gives you valid combinations.

 

Disclaimer – I make no claims to being an Excel expert. This has been developed using the functions of Excel which I know well. There could be a better way. If anyone knows one, fine.

 

If anyone does get a version working for their own needs or has a better way feel free to post it with a description.

 

Disclaimer 2 – If you can’t get a version working please don’t ask me to sort if for you. This system is released “unsupported” – computer-speak for “Son, you’re on your own.”

 

Have fun with it and let me know what you think.

 

Thanks to Martin for documenting his system so well that turning it into a spreadsheet was a doddle, for testing it for me and letting me start a thread about it.

 

Regards

 

Cam

 

P.S. – some people prefer low-tech ways of doing this (pen and paper / playing cards / 100-sided dice / crystal balls / divination). Let me paint a mental picture for you. A OO-gauge, pre-grouping layout. Trundling along the track is a rather fetching 0-6-0ST pulling a rake of quaint four-wheel carriages. That rather fetching tank engine is trundling down the track using more computer power than was used to get the Apollo astronauts to the Moon (if it’s DCC). Low tech? I think not.

Freight Train Generator No Macros.xlsx The Excel Freight Train Generator.docx

Edited by CameronL
Hadn't finished.
  • Like 5
  • Thanks 2
  • Craftsmanship/clever 3
  • Friendly/supportive 2
Link to post
Share on other sites

  • RMweb Gold

An excellent idea and well explained.

 

Can I make a suggestion or two ?

 

In this paragraph :

 

Quote

 

The last thing to do was amend the random number generators on the “Train” sheet to reflect how many lines there  now are in the Weighted Wagon List table. Each cell in Column C reads =RANDBETWEEN(1,54). It needs changing to =RANDBETWEEN(1,56) and copying to every cell in that column that has a command in it.

 

 

you state that each line needs to change to replace '54' with '56'.

 

If you have a fixed cell  (eg M1) and set the value in there to '56', you can change the other statements to =RANDBETWEEN(1,$M$1).

Thus anytime you need to add more wagons, just change the value in M1 to suit.

 

I also think the vlookup statements can have the end-of-range-value set to something like 1000, without adversely affecting the function :

Quote

 

=VLOOKUP(D17,$Z$16:$AB$1000,3,FALSE)

 

 

This again will help when expanding the tables.

 

Hope that helps

 

Stu

  • Like 1
  • Agree 1
  • Thanks 1
  • Informative/Useful 1
Link to post
Share on other sites

  • RMweb Gold

One other point, to which I don't as yet have an answer, is the train of eight wagons would normally have the wagons for each destination grouped together, ideally with the rest of the train ordered in the destination order as well. Other wise the shunter has an inglenook puzzle to solve when marshalling the train for departure :) 

  • Agree 2
Link to post
Share on other sites

12 minutes ago, Stubby47 said:

One other point, to which I don't as yet have an answer, is the train of eight wagons would normally have the wagons for each destination grouped together, ideally with the rest of the train ordered in the destination order as well. Other wise the shunter has an inglenook puzzle to solve when marshalling the train for departure :) 

Yes, he does! I am finding sometimes more time is spent assembling trains than their journey times. Having them in destination order would help, but that is an immense bowl of spaghetti as regards variables.

BTW, thank you very much Cam, beautifully done and well explained.

  • Thanks 1
Link to post
Share on other sites

  • RMweb Gold

Given that a train will be generated for a specific yard, each route planned from that yard will have a fixed list of destinations.

Therefore, by firstly picking the random train, then selecting the yard/route, it should be possible to then re-sort the train into the required order.

 

What happens though, for example, when the 3 fruit vans are elsewhere on the layout and a train calls for a fruit van to be added? Is another van substituted? Is the 'van' left off the train?

Link to post
Share on other sites

On 02/08/2019 at 14:02, Martin S-C said:

Yes, he does! I am finding sometimes more time is spent assembling trains than their journey times. Having them in destination order would help, but that is an immense bowl of spaghetti as regards variables.

BTW, thank you very much Cam, beautifully done and well explained.

Maybe re-ordering the selection into destination order could be the Mark 2. Anyone fancy having a go?

 

I think this could be achieved with a macro but RMWEB wouldn't let me upload it. 

 

Maybe this is a case of using "appropriate technology" and rewriting the list on a whiteboard to go with the train. 

 

Martin, it was fun to do. (Does that make me sound like IT Geek of the Week?) Hopefully I'll be able to put it into practice myself in the future.

 

Cam

Edited by CameronL
A few further thoughts
Link to post
Share on other sites

On 03/08/2019 at 00:47, CameronL said:

Maybe re-ordering the selection into destination order could be the Mark 2. Anyone fancy having a go?

I think this could be achieved with a macro but RMWEB wouldn't let me upload it. 

 

Cam,

 

you've been spared by the RMWeb rules! Writing macros in Excel that someone else will use is a nightmare in the making. A cell changed, row inserted etc and the whole thing collapses. Only option then is to tie it so tight with security that it loses its flexibility. Lesson learn in my working life!

 

Colin

 

 

 

 

  • Agree 1
  • Thanks 1
Link to post
Share on other sites

On 02/08/2019 at 13:48, Stubby47 said:

One other point, to which I don't as yet have an answer, is the train of eight wagons would normally have the wagons for each destination grouped together, ideally with the rest of the train ordered in the destination order as well. Other wise the shunter has an inglenook puzzle to solve when marshalling the train for departure :) 

 

I guess you could use your solution for above and use a randbetween 1 and n to pick the number of wagons for a given location?  

Eg Col A selects a destination using a random generator and a lookup table of locations.  Col B sets the number of wagons to that location.  columns c onwards have another random generator to pick the type of wagon to that location.  The formula in col c onwards could say if the col count (i forget the exact function that gives you the number of the column from left to right) is less than or equal to the col b answer, then do the wagon choice else return a blank cell “”.

 

fun little problem!

 

David

  • Like 1
  • Thanks 1
Link to post
Share on other sites

  • 2 weeks later...

Brassey said -

 

"Your freight system does not seem to take account of fitted and unfitted trains, unless I've missed something "

 

No it doesn't.  I developed it using the information from Martin's thread, which made no mention of fitted / unfitted freights. He has since mentioned that it's not an issue on the NM&GS as all freight will be unfitted apart from NPCS wagons which will be attached to passenger trains anyway.

 

(I also gather from his latest posting that changes have already been made. Best of luck with that, Martin. PM me if you have any problems).

 

However, thanks for the question as it got me thinking. How would you incorporate fitted / unfitted into the system?

 

There's more than one way to do it, depending on what you want.

 

1) In the case where you want to run unmixed trains that are all either fitted or unfitted the easiest way would be to simply duplicate the "Tables" tab in the spreadsheet so that there were two versions; "Fitted" and "Unfitted". Each would contain on its "Wagon Types", "Weighted Wagon List" and " "Full Destination / Load List" only those wagon types which were either fitted or not. Each of the tables on the "Train" tab would change from "Train 1" and "Train 2" to "Fitted" and "Unfitted", refer to either the "Fitted" or "Unfitted" tabs and Shazam! You're suddenly generating wholly fitted or unfitted freight trains. Wagon types could appear in both tables depending on whether or not you have a mix on your layout.

 

2) In the case where you want to run mixed trains with a fitted head you would only need one "Tables" page but with some amendments to the "Weighted Wagon List" table. An extra column would be added with the values of either "F" or "U" depending on whether the wagon was fitted or not. 

 

image.png.3260929d0b4e6519c3849b49b71e1444.png

 

Please note - the numbers of "U" and "F" values are different. This can be used to reflect the relative numbers of each type of coal wagon. 

 

Then there's a simple addition to the "Train" tab - a column to display the "F" or "U" value - 

 

image.png.43cfd5d3464392d126394a7a9a2e3aa8.png

 

Please note the "VLOOKUP" command. It uses the random number generated  in Column C to lookup the value in the new fourth column of the "Weighted Wagon List" table.

 

Of course then you've got an uber-Inglenook problem of assembling the train not only in destination order but whether a wagon is part of the fitted head or not. This is one for the Shunt Hogs among us.

 

I've attached a copy of the spreadsheet if anyone's interested. It didn't actually take long to amend, and I worked out how to do it on a dog walk.

 

Have fun.

 

Cam.

Freight Train Generator FittedOrNot No Macros.xlsx

  • Like 2
Link to post
Share on other sites

  • 10 months later...

Hi All. 

 

Here's the Mark 2 - all singing, all dancing and possibly juggling chainsaws.

 

I know it's some time since I was on this thread but there's been a lot going on. There's also been a lot of time to consider my own plans and have a re-think, which is as follows -

 

1244586257_PlanFinalwithfreightdestinationsjpeg.jpg.accb333019c7580ca19e2408c7dc2e90.jpg

 

This is a plan for a system layout based on a fictitious island called Lagganholme. I've talked a little bit more about it here but basically it's a small island with a fairly extensive railway network, fed from the mainland by a train ferry. Coloured purple are all the freight destinations / industries that might receive wagons during an operating session (a huge potential for freight movements, in other words). The train ferry would act as as fiddle yard, bringing in freight from outside the system which would then be sent on to other destinations. There would also be potential for various movements around the island from industries in one location to customers in another. 

 

So a Mark 2 was called for,  and what's more it does order the wagons by destination, which the Mark 1 didn't. If you're interested I've attached the spreadsheet that does it and a functional description of how.

 

Have fun. 

 

Best wishes

 

Cam

 

Functional Description.docx Lagganholme Freight 2.xlsx

  • Like 5
Link to post
Share on other sites

  • 5 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...