Rearranging timetable data

  • Hi,

    I have a challenge with a public transport timetable (file "New Timetable" attached). In the attached file there are two tabs "Zone 3 to Pta" (this is an example of how the timetables currently look like) and "Solution" (the format in which I now need it - I only included 3 lines as example). The timetables can contain more columns as well as rows. As can be seen there are various numbers of "intermediate points with arrival times attached.

    What is important (and I don't know whether it is possible) I need the name on the tab as part of the "Solution" as shown in Column A.

    I need to do this for between 50 and 100 timetables of a similar format and by hand it will take days.

    Is it possible to write a macro which will convert the info from the one format ("Zone 3 to Pta") to the new format as shown on "Solution"?

    Your guidance and assistance will be appreciated.



  • Re: Rearranging timetable data

    What your asking can be done in VBA reading each sheet as a object but would need a sample sheet with contained least two zone's of data to tinker with.

    As far as converting time without VBA, if all your data is based on ##H## values, you can use a formula.

    Example, if Cell A2 = 08H30 then the formula would be =Time(Left(A2,2),Right(A2,2),0)

    Your results would be 8:30 AM

  • Re: Rearranging timetable data

    Hi Fuzz-Head,

    Thanks for the reply. Do you mean you need a sheet with at least two tabs with timetables as attached (timetable example.xls)? You will see on the attached spreadsheet that there are a top and bottom table on the tabs which is compiled like this for easy printing. In some cases the top table is the "forward" trips and the bottom the "reverse" trips.

    Just on the time issue: is it possible to do it on 24hr format in stead of adding am/pm?

    Hope this will assist.



  • Re: Rearranging timetable data

    Here you go, see if this fits your needs.

    You now just can add new Zones to this sheet. Use the Ref tab for any notes.. Do not add any other sheets than zones in the format from the samples you provided.

    Here's the Code, Little sloppy on my part as had to finish before landing but I think it does trick.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!