Punctuality Log , final results guideance/advice

[jkility]

Hi guys

I posted a few weeks back looking for some help with times etc in Excel and got my answer. Now I have all the data i require (for a bus punctuality log) im looking to compile all the results into 1 page but just not sure how exactly I would go about doing it. I've attached it to this thread and just looking for some help and advice as these results will be published to the media on 11th March.

As you can see from the "halfway results" all the data entered is hand entered and I was looking to do the final results in a similiar way, with having each bus number and having the amount of submissions made on that bus, how many times it was late and early, amount of total mins it was late, amount of total mins it was early and a average on how late it was etc. I also want to be able to add the total lateness of all the buses submitted on the spreadsheet (I can do that already).

I was just looking for advice on the best way to do this, Thanks in advance.

[jkility]

Pls take some minutes and read forum rules. Then change your title according our rules.

Then anyone will be able to helps you.

Changed it for you

[jkility]

bumpy bumpy

[alvin-chung]

Hi jkility, could you at least show 2 rows of expected result in the spreadsheet with some description so that members have better grasp of your criteria and try to help out?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

[QUOTE=alvin-chung;3606395]Hi jkility, could you at least show 2 rows of expected result in the spreadsheet with some description so that members have better grasp of your criteria and try to help out?


Hi Alvin

Sure, I've started doing the results of each day, ready to be put into one big results page at the end. Once I've done the first page results I'll upload so gives a better understanding on what I want t o achieve.

[jkility]

Hi

Just re-uploaded the spreadsheet, If you look at page 3, Im going to have results like that on each page and then what I want to do is have all the results gathered up together on a page with averages over the month etc, total entries etc, also would like to have results which show's all the buses and how many submissions there have been for each one, overall lateness etc etc similiar to whats on the "Halfway Results" page

[alvin-chung]

Just to be clear, are you asking for solution to to display the result (numbers and time) shown in sheet 3 E23:K34?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

Just to be clear, are you asking for solution to to display the result (numbers and time) shown in sheet 3 E23:K34?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin

Exactly that! Im doing the slow process of doing what I've done on sheet 3 E23:K34 to every page so it will make easier work of the results page, just want guideance/advice on best way to do so and easiest way to do so. Thanks

[alvin-chung]

Does the data for each sheet will always in the range of D4:N21 with exactly same format and sheet 3?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

Does the data for each sheet will always in the range of D4:N21 with exactly same format and sheet 3?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Same format yes, each sheet will have a Timetable column, Arrival Column, Late Column and Early Column which will be in the time format. While Service, From, To will be in text format.

[jkility]

Just uploaded another version to the first post, this version has the results of each page on each page.

[alvin-chung]

Hi jkility, after glance through your 28 individual day sheets with consideration that you would need to consolidate them into a master table I would personally go for vba in long term.

Nevertheless, you may opt for initiating formulas in Sheet 1 (you can use COUNTIF(S), SUMIF(S) and/or SUMPRODUCT) then copy paste to rest of the sheet instead of having to manually summarize it.

Note that though your data always start from row 4 with consistent columns but the number of rows vary from sheet to sheet. Regardless using vba/formula, consider design your summary data for each sheet to start from a specific row that has enough room for the data growth. For example so far Sheet 12 has the most data (i.e. 22 entries), thus I would start the summary data from row 30 consistently for each page, i.e.

E30 = "Total Entries"
F30 = COUNTA(E5:E28)
etc..

Once you're done with 1 sheet, you can simply copy the them to rest of the sheet

ps: Another reason that I'm suggesting to place your summary data consistently across each page because that will ease your work when you're working on master sheet.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

Hi jkility, after glance through your 28 individual day sheets with consideration that you would need to consolidate them into a master table I would personally go for vba in long term.

Nevertheless, you may opt for initiating formulas in Sheet 1 (you can use COUNTIF(S), SUMIF(S) and/or SUMPRODUCT) then copy paste to rest of the sheet instead of having to manually summarize it.

Note that though your data always start from row 4 with consistent columns but the number of rows vary from sheet to sheet. Regardless using vba/formula, consider design your summary data for each sheet to start from a specific row that has enough room for the data growth. For example so far Sheet 12 has the most data (i.e. 22 entries), thus I would start the summary data from row 30 consistently for each page, i.e.

E30 = "Total Entries"
F30 = COUNTA(E5:E28)
etc..

Once you're done with 1 sheet, you can simply copy the them to rest of the sheet

ps: Another reason that I'm suggesting to place your summary data consistently across each page because that will ease your work when you're working on master sheet.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Thanks Alvin

One last question, How exactly would I go about doing that (Im not a excel expert )

[jkility]

Or infact anyone that would have an idea on the best way to do VBA and a master sheet.

[alvin-chung]

Hi jkility, you're welcome.

Post #13 was basically an on the best way to work towards vba, try searching in the forum/internet for the formulas that you requires technically (such as the COUNTA formula demonstrated in post #13).

In short, I would recommend setting up your data/formula consistently across all individual day sheets for a start.


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

Hi Alvin

Unfortunatly im getting no where with this, It's not making much sense to me because what they are trying to consolidate is nothing useful to me.

On each page I may have the same service several different times, I just want to bring that together with the total lateness, how many submissions that bus has had, how many times its been late, early, ontime, or not shown up etc But I just cant seem to find how without doing it all by hand, dont mean to sound dumb, but are you able to provide some assistance in how I would easily do this??

[alvin-chung]

Hi jkility,

I could guide/share you how to work towards your goal (as shown in post#3), but not develop the coding from scratch.
Perhaps for a start, had you tried the formula that I provided in post#3 for Sheet "1"? Do you know VBA?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

Hi,

If you could that would be great, I know nothing about VBA unfortunatly, just something basic to get me going would be great really

If you look at the latest log I've just uploaded (previous post) I just want something a bit like the halfway results, but something more professional/easier to do and look at etc

[alvin-chung]

Hi jkility, I'm afraid that's the best I could assist so far.
You might want to read up more about vba and initiate code for your project, then post a new thread whenever you encounter difficulties with the coding...


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[jkility]

An update to this,

A version of what I've been looking for has been completed but the person said they dont have enough to finish now and infact it's not all complete/right, for example service 1 has 28 entries, SHOULD be 15 times late, 8 on time and 5 no show, Just need assistance in getting this right really if anyone could help? (it's attached)

[alvin-chung]

Hi jkility,

At a quick glance, the code missed out quite some entries that's why you couldn't get the full result. Change all "j-5" to "j-4" should yield more appropriate results. Having to say that, the counts for Service 1 still showing correctly. Not sure how you get the counts as stated in post#20?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin