CameronL Posted August 2, 2019 Share Posted August 2, 2019 (edited) 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 August 2, 2019 by CameronL Hadn't finished. 5 2 3 2 Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted August 2, 2019 RMweb Gold Share Posted August 2, 2019 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 1 1 1 1 Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted August 2, 2019 RMweb Gold Share Posted August 2, 2019 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 2 Link to post Share on other sites More sharing options...
Martin S-C Posted August 2, 2019 Share Posted August 2, 2019 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. 1 Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted August 2, 2019 RMweb Gold Share Posted August 2, 2019 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 More sharing options...
CameronL Posted August 2, 2019 Author Share Posted August 2, 2019 (edited) 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 August 4, 2019 by CameronL A few further thoughts Link to post Share on other sites More sharing options...
BWsTrains Posted August 4, 2019 Share Posted August 4, 2019 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 1 1 Link to post Share on other sites More sharing options...
Clearwater Posted August 4, 2019 Share Posted August 4, 2019 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 1 1 Link to post Share on other sites More sharing options...
CameronL Posted August 15, 2019 Author Share Posted August 15, 2019 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. 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 - 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 2 Link to post Share on other sites More sharing options...
CameronL Posted June 20, 2020 Author Share Posted June 20, 2020 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 - 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 5 Link to post Share on other sites More sharing options...
malc60015 Posted December 20, 2020 Share Posted December 20, 2020 Hi Cameron, Just come across your post, I will have a go at in the near future. If (when) I come unstuck is it ok to contact you. malc Link to post Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now