Model Railway Timetable Excel Spreadsheet – autosort help

Topic

New Reply

I’m a model railway hobbyist and I’m using Excel to create a timetable display. I’m using Excel 2003 and through the use of many, many, MANY if statements, I have created a timetable that updates automatically including the weather, train updates when they are available on line. I’ll put up a print screen of the document, but I’m looking to have the timetable automatically sort by the next expected train (hence sort by time). Unfortunately, I’m unable to figure out any method of accomplishing this.

Can anyone provide some suggestions?

Merci,31321-timetablesample

Reply | Quote

Replies

I will need to see a sample worksheet showing a before and after, not a screen shot.

Steve

Reply | Quote

31322-écran-nouvelle

For aesthetic purposes, some of the cells are merged together.

Reply | Quote

I am lost in your spreadsheet. If this is the “before” what should it look like after and what is the logic?

I don’t see the patterns in the formulas of Tableau.
Rows 4-19 have a pattern of increasing by 6 columns every 3rd row, then in rows 22-37 they just seem random.
Why do some cells not have formulas (D4, D22, F22, I22, K4, K7, K10, K13, K16, K19, K22, K25, K31)?
Why are the formulas in I28, I34, I37 a different type than the others in Column I?
The formulas in M7, M10, M13, M16, and M19 share a pattern, all other formulas in M are unique. Why?
Is there a reason rows 40-52 seem to part of the region, but have no formulas?

I am just lost to this design and what you need exactly.

Steve

Reply | Quote

I’ll post a link to a picture of what I’m looking to do.

http://www.robots.ox.ac.uk/~mebden/images/dec2008/15%20Union%20Station%20Toronto.jpg

You’ll notice that the departure times are in chronological order. When you’re in the person at Union Station, the display updates automatically. So when 15h10 strikes on the clock, the next train time takes its place on the top (so in this case, train 46 Ottawa at 15h30) & so on & so forth. This has been my dilemma. I can’t figure out a formula (without resorting to tons of If statements on top of If statements) that allows for the trains to be sorted by time & automatically update.

There are four tabs where the information is being pulled from the Excel document. There are only some trains where I am able to pull train status updates on line, so the ones wit Aucune Mis-à-jour disponible means no updates available.

The document self updates. D column is for weather forecasts at the moment. So some of the locations simply don’t have live forecasts. K column is for the new time when the train is scheduled to arrive should there be a delay.

Reply | Quote

Your picture does not answer my question of what you want the example data to look like. Please attach the output of what you want based on the example data. You have also not answered any of my questions about the inconsistencies in the formulas of the example workbook. In addition to my first question about the pattern of columns, you seem to not use some of the “6-column sets” in the output, and I don’t understand why.

Perhaps someone else can figure it out, but if you want my help, you will have to clarify what you will start with and what you want the output to look like at the end.

Steve

Reply | Quote

Nosi,
I had written a detailed explanation on some steps to resolve your problem but my response got erased during the post. Here, however, is a general overview of a work around. I understand what you are trying to achieve and it is very, very cool. You obviously spent a lot of time building the workbook. The Schedule sheet (Hoaires) was clevery crafted to detect the location/arrival of each train. After that, like sdckapr, I could not follow the flow through the Delay (Retards), Weather (Meteo), nor the MAJ (?) sheets. sdckapr also commented, there are many inconsistancies in the patterns of formulas in Tableau and I agree. These should be repaired before you continue further. Written en francaise didn’t help but certainly not your fault. I would restructure the entire workbook into 3 sheets: The Interface (as you did, Tableau), the TrainInfo, and the Schedule (Hoaires- keep it because it works). In the TrainInfo sheet, I would better organize all the properties of each train, including the weather, delays, etc. Setting it up as you would build a table of a database and draw from there. Visual Basic would eliminate much of the nested conditional statements

Resolutions:
1. Your dilemna involves resorting the information on your interface sheet (Tableau) according to Arrival time without loosing reference to the cells from your other sheets. You should have used absolute cell referencing to avoid this. Example: F7 formula (Tableau sheet) =(Horaires!AZ58) should be changed to =(Horaires!$AZ$58). Apply this to every cell on Tableau Sheet except Remarques. Now if the lines are resorted by arrival time, they will retain their precedents.
2. The following code will sort Tableau by arrival time, then update it every 30 seconds:

Visual Basic- The following code must be placed in ThisWorkbook
ThisWorkbook

Private Sub Workbook_Open()
’Update Tableau 30 seconds after opening workbook then rum MYSchedule routine
Application.OnTime Now + TimeValue(“00:00:30”), “MYSchedule”
End Sub
————————————————————————–
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
’Update Tableau 30 seconds after any changes made to workbook then rum MYSchedule routine
Application.OnTime Now + TimeValue(“00:00:30”), “MYSchedule”
End Sub
————————————————————————-

Following code MUST be placed in a module
Module 1

Sub MYSchedule()
’Sort Tableau according to Arrival time
ActiveWorkbook.Worksheets(“Tableau”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Tableau”).Sort.SortFields.Add Key:=Range(“I7:I38”) _[INDENT] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/INDENT]
With ActiveWorkbook.Worksheets(“Tableau”).Sort[INDENT].SetRange Range(“B7:N38”)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply End With[/INDENT]
End Sub
————————————————————————————————–

The only obstacle that you must deal with are the separating rows in between each train on the Tableau sheet. They will be filled with information during the sort. Either remove them AFTER absolute referencing or write additional code to deal with these uneeded rows.

HTH

Reply | Quote

sdckapr, Maudibe,

I appreciate you both taking the time to respond & solve the issues I’m running into when creating this document. And thanks for the compliments. With respect to the delays, the weather, the “MAJ” & the inconsistencies;

MAJ / Remarques
Only certain trains by a particular provider offers updates (aka MAJ or “Mis-à-jours”) Trains 421/422, 697, 698 & TRT. So for the trains that offer online updates (where I can actually pull the information into the excel spreadsheet), the information is displayed in the MAJ column (whether it is on time (“à l’heure”), delayed (délai) or canceled (annulé)).

Delays
I am reformating this function, but the purpose of this column is to provide the new estimated time of arrival should a train be late. Again, this information is only available for trains 421, 422, 697, 698 & partially with the TRT. There is a tab to convert the delay from the website into an actual time so that it displays approximately.

Weather
Because most of the trains I am displaying are based in or pass through Northern Ontario, many of the areas where the train stops simply don’t have weather forecasts provided at all (primarily because some of these stops are located in the middle of the Cambrian Shield – middle of the forest) and there is no perminent population that lives there. The more densely populated areas (trains 697-698, 1 & 2 west of Winnipeg) have more areas where forecasts are provided. So, the reason for the inconsistencies with them is that Environnement Canada simply doesn’t provide this information. One of the issues with pulling this information from the web is that I’m unable to filter a particular section of the page I want selected (ie. the temperature). So, hence the reason I have a dedicated page for weather & the process of filtering out the information simply because of the sheer volume of the data.

One of the biggest challenges I faced with this was determing how to sort the train information (particularly with the transcontinental trains #1 & 2). #421, 422, 697 & 698 run generally 5-6 times a week (hence almost daily except saturdays). 185 (tues, thur, sat), 186 (wed, fri, sun), 631 (mon, thur, sat), 632 (tues, fri, sun) operate three days a week in each direction.

#1 & 2 are by far the most complicated trains to include in this schedule. These trains run across to Western Canada & the trains operate continuesly throughout the day & night (24 hours). There is also the issue that many of the trains operate only on certain days of the week (hence some of the inconsistencies).

The transcontinental trains run in each direction 3 times a week. #1 departs Toronto Tues, Thur & Sat. #2 departs Vancouver Tues, Fri, Sun. Sometimes there are two trains with the identical number but at different spots (again with #1 & 2). Since there is about 4500 kms of distance between Toronto & Vancouver, one train could be out in British Columbia, the other just entering Northern Ontario. There are other seasonal trains which operate only during certain months. There are yet more complications with some of the trains switching schedules partway through the year for the summer, and then switching back for the winter (ie #631/632).

Are there any tutorials on how to use Visual Basic, absolute cell referencing & macros? I’ve attempted to apply formulas in the past, but have had little success because I don’t quite understand how it works.

Reply | Quote

For some websites see the references in http://windowssecrets.com/forums/showthread//94949-Select-Cells-that-are-Locked-(2003-sr2)?p=543100&viewfull=1#post543100. I don’t know if they are still valid.Since you haven’t provided any samples of downloaded data and what you want the output to look like (or even answered my specific questions), I will sign off and let Maudibe take over. S/he seems to have a better understanding of what you are after. Sorry I couldn’t help more…Steve

Reply | Quote

Nosi,
When you reference a cell (cell A) from another cell (cell B) then move the contents of Cell A, such as a sort, the reference pointer moves with it. An example would be, cell A1 gets its data from cell B1 using the formula “=B1”. If B1= “Hello” then call A1= “Hello”. Obvously, you know this. However, if you move the contents of A1 to A5 using a sort, the pointer will move along with it and draw the information now from B5 which may contain totally different information. To keep the pointer to the same reference cell when moving cell A1, the formula in A1 should be changed to “=$B$1”. No matter where you move the contents of A1 via a sort, it will always point to cell B1. This works even if you are drawing from a cell on another sheet as you are.

As far as visual basic, it is so easy that I self taught myself. If you surf the internet, I am sure you will find free tutorials. Interestingly enough, starting with Excel 97, Microsoft employed Intellisense in visual basic in which you place a dot (.) after an oject such as a form, list, button, etc., when writing code. Intellisense will give you a list of all proprties, methods, and events that can be selected. If you were to record various macros (aka procedures, routines, but not functions) then view the macro in visual basic, you will get an idea of how VB works. In Excel 2003, you can access macros and visual basic by the tools menu>Macro. In 2010 (I guess the same for 2007), both are accessed on the ribbon by the Developer tab. If the Developer tab is not available, you have to add it to the ribbon: File>Options>Customize Ribbon. Select All tabs from drop down box then add the developer tab from the left list box to the right list box. VB is a topic unto itself which cannot be covered here.

Visual basic is a necessary language to learn to extend the functionality beyond just a spreadsheet. One can cleverly craft a spreadsheet using VB and you would think it was a Windows program with not one cell visible in its interface and total funtionality of a full application. It looks like you have pretty much mastered the frontside of Excel, now delve into the back side of adding visual basic to it.

The above code will work in your spreadsheet along with Absolute referencing but the sandwiched lines in between the trains presents a problem during sorting as they get filled with data as well. Try making a copy of your work than make the changes I descrbed: remove the rows in between the trains, add the absolute cell referencing, and add the code.

HTH,
Maud

Reply | Quote

Nosi,
Since you are not familiar with VB, I have made the changes I described above:
1. Changed cell referencing to absolute cell referencing except for Remarques column, rows 1-5, and rows >=40
2. Modified the VB code when sorting to take into consideration the blank rows between the trains on “Tableau”
3. placed the code in the appropriate areas.

So far it is working well, auto updating every 30 sec. and 30 sec following the web update. You may notice rows40-41 that have formulas. This is just a temporary swap row and is hidden. I am trying to figure out a way to sort the arrival times when there are both times before and after midnight present. By order of arrival, a train arriving at 23:00 should be above a train arriving at 1:00 (after midnight) on the display. But when you sort them, 1:00 will be above 23:00. Because the times do not have an associated date, a sort will always think 23:00 is after 01:00 which is not true if 23:00 is from the day before midnight not from the same day. Any thoughts on the logic?

Maud

Reply | Quote

31457-Train1 31458-Train-2 31459-Train-3
Here are 3 serial screen shots after trains updated.

Just an after thought. I edited it in Excel 2010. I hope the code I wrote is backward compatable with the version you may have. If not, I will revise it to work with your edition.
Maud

Reply | Quote

Hi Maudibe,

I will test out the document when I arrive home this evening, but wow, I’m impressed. One solution that I have fiddled with to account for the time being after midnight is to specify in source schedules page (Horaires) to specify (for example) Kamloops Nord/North that it arrives at 26:09 instead of 02:09 (at least until the clock strikes midnight). Excel recognizes the cell as 24 hours (if specified) and thus the 2:09 train should appear after a train at 23:30 (say Winnipeg).

Thanks again! Merci beaucoup!

Nosl

Reply | Quote

Hi Maudibe,

I’ve reviewed the document, and I’m unsure whether or not the issue I’m having is because of Excel 2003.

The only trains that seem to be changing positions are the #1 & #2 trains. I’m not seeing any updates with any of the other trains. I am tweaking the document to remove any of the merged cells to ease with being able to update.

This is an example of an actual arrival/departure marquee that I’m looking to model:

http://gotransitnlb.gotransit.com/publicroot/en/unionstation/departures.aspx

Reply | Quote

Nosi,
As I was playing with your work of art, it was becoming more apparent what you were doing and how you were doing it. I realized that a lot of the data on the additional sheets were just collateral information collected from your web query and much of it not used. It all started to make sense. I took a look at the link you posted for the marquee. It would be SO less complicated if you redisigned your interface to mimic it without the additional rows used as spacers between the trains. In Excel 2010, it works flawlessly. I even wrote additional code to address the train slots that are blank to move them to the end. Some of the code for 2010 may not be backwards compatable with 2003 since 2003 is based in Visual Basic while since 2007, it has been based on Visual Basic .Net. It may also be a setting within Excel.

If you tweak your face sheet to look like the linked marquee and remove the extra rows, I will make adjustments so it runs in 2003. So, get it the way you want the final product to look, USE ABSOLUTE REFFERENCING with your formulas, and I will do the rest. This is such a neat concept and the work you put into it is ingenious; it would be a shame not to complete the last final stages. I will also include a line to line explanation of the code so you can follow. Tinker around with the date before/following midnight issue. Perhaps, a hidden (white-on-white) column that includes the date then I can take it from there. I check back from time to time so I will see it when you post.

Attached, are very simple directions on how to stop the auto-updating so you can work on it. Get into visual basic by my instructions in previous post. Once there, doubleclick on “This Workbook” on left indicated by the red arrow. A window will open with the code. Place an apostrophe ( ‘ ) without the brackets in front of the lines I indicated. The lines will turn green as shown (they are now just comments and not code), then “save” icon in the VB window. You can now close the VB window and work on the spreadsheet. Save as normal. Once it is completed, I will show you how to lockdown the code so no one can take credit for your work.

Maud

Reply | Quote

Hi Maudibe,

I’m about 2/3 complete with the conversion to absolute referencing. Much of what’s left involves adjusting the weather & delay formulas. I have tweaked the final layout and I have removed the spaces in between.

I should have the final copy ready by the end of the week

Thanks again for your help.

nosl

Reply | Quote

NP, will look for it

Reply | Quote

Hi Maudibe,

Attached is the revised copy with absolute formating of the formulas.31790-Écran-en-développement

Reply | Quote

Nosi,

Will take a look at it. Give me some time to change the coding.

Maud

Reply | Quote

Nosi,
I like it so much better. You are handling the conditional statement for the remarks (Remarques) on the Horaires sheet. Excellent! That meant that all the remark cells require absolute referencing as well. The new workbook has removed the Direction column and added a column for Gate (Porte). Some of the referencing needs to be changed. Here is one of your formulas (Cell K5 left to right):
=IF($J$5=$C$2,Horaires!$BF$66,IF($H$5=”EMBARQUE / BOARDING”,Horaires!$BF$66,IF($H$5=”ARRIVÉE / ARRIVED”,Horaires!$BF$66,””)))
1. $J$5 needs to be change to $J5 because once sorted, the formula will move to a different row as the rows shift. The cell needs to look to the column to its left which will still be J so $J is OK (optional) but the row may no longer be 5. If you left it as $5 and the formula now is sorted to row 10 with the rest of the train data, it would still be pulling information from J5 instead of J10
2. $C$2 is correct as the cell for the current time will not change and needs to remain fixed.
3. Horaires!$BF$66 Is correct as it remains fixed
4. $H$5 needs to be changed to $H5 because the row will be changing (same as #1).
5. Horaires!$BF$66 Is correct as it remains fixed.
6. $H$5 needs to be changed to $H5 because the row will be changing (same as #1).
7. Horaires!$BF$66 Is correct as it remains fixed.

The final formula should look like:
=IF($J5=$C$2,Horaires!$BF$66,IF($H5=”EMBARQUE / BOARDING”,Horaires!$BF$66,IF($H5=”ARRIVÉE / ARRIVED”,Horaires!$BF$66,””)))
The same applies to K6 through K19

Cell J5:
=IF($H$5=”ARRIVÉE / ARRIVED”,””,Horaires!$BE$66)
8. Because this cell (no matter what row it ends up in) needs to look at the same formula to its left which will shift with it. However, $H$5 may no loger be the cell to its left. For the same reasons as #1, it needs to be change to $H5
9. Horaires!$BF$66 Is correct as it remains fixed.

The final formula should look like:
=IF($H5=”ARRIVÉE / ARRIVED”,””,Horaires!$BE$66)
The same applies for J6 through J19

Delay column and column F has the same issues. The bottom line is:
A. As long as there is no sorting, your formulas will work as is but if there is sorting then B and C apply.
B. if the train is pulling information from another sheet, it must be fixed so absolute referencing is a must ($ for row, doesn’t matter for the column since the cells that shift will always remain in thier respective columns),
C. If the train is pulling information from cells that are going to be shifting, relative referencing is a must (NO $ for the row, doesn’t matter for the column since the cells that shift will alway remain in thier respective columns).

I must apologize if I lead you to think that you needed to change all the cells in your workbook to absolute referencing. It was only on the Tableau sheet where the shifting cells needed to refer to a specific [/B]cell elsewhere. All the absolute referencing you did on the other sheets was not necessary since they are all static but it will not hurt.

I will make the changes as I described then begin work on the coding.
I wish we could exchange email addresses for more efficient communication and protection of your work instead of posting here.

Maud

Reply | Quote