Macro to sort data into totals & %'s

[rushdenx1]

I need some help with the enclosed file. The Data tab contains information on lorry journeys between locations, together with their departure and arrival times. This data is added to on a daily basis and therefore the total number of rows could increase to any amount. At the end of each week the Macro 'FormatFilterSortAndPasteToSheet' is run, which filters and sorts the data, so that all the movements associated with each 'letter' (column C) are listed together under each of the corresponding lettered tabs. What I now want to do for each 'lettered tab' is the following:

1/ Delete any entry in the HC column that starts with a '0' (this can be done before the
above Macro is run, if that helps).
2/ For each 'HC',the total number of times that 'HC' ran. ie 4E33 = 3.
3/ Of the total for each 'HC' how many arrived within 0-10 mins of booked arrival (column L)
ie 4E33 = 3.
4/ The above two figures as a %. ie 4E33 3 ran, 3 on time = 100%.

This information can appear in columns to the right of the existing data under the lettered tabs or on separate worksheet(s), whichever is easier. Any help is appreciated. Thanks Kevin.

[foxguy]

Kevin;

Didn't do a lot of debugging (left that to you), so back up first Copy of Performance Data Dump.zip.
Had to zip it because the results made the file too big to upload.
I put the additional code inside FormatFilterSortAndPasteToSheet()
I put the results into columns R:U on each tab.

[rushdenx1]

Thanks Foxguy for your quick response. A couple of things:

1/ When adding up the total number of trains for each HC, if Column L is blank, do not count these as part of total, only cells with a number in.
2/ I made an error about the definition of trains 'on time'. It is any train arriving 10 minutes or less, which includes negative numbers, which are in fact arriving early.
3/ Any entry in HC (column B) starting with '0' not to be included in totals (if that's too difficult don't worry about it).

Thanks again for your help. Kevin.

[foxguy]

Kevin;

I abhor ever deleting data, so I left it alone, just didn't count it.
Try this file Copy of Performance Data Dump1-2.zip I didn't debug it at all. Didn't have time. If it has a bug, it'll have to wait until tomorrow.

[rushdenx1]

Thanks Foxguy. Everything looks good.

One last request. The 'ran', 'on time' and '% on time' has been done for each HC. Can this also be done for each ORIGIN (column F). This will then give me the number of journeys from each location and their 'on time' and '% on time' figures. On thing to bear in mind is that some locations appear under more than one 'lettered' tab. 'Port of Tyne' as an example appears under 'F' tab and 'N' tab. I would like a separate total under each tab for each location.

Thanks again. That will complete my request. Kevin.

[foxguy]

Kevin;

One last request. The 'ran', 'on time' and '% on time' has been done for each HC. Can this also be done for each ORIGIN (column F). This will then give me the number of journeys from each location and their 'on time' and '% on time' figures. On thing to bear in mind is that some locations appear under more than one 'lettered' tab. 'Port of Tyne' as an example appears under 'F' tab and 'N' tab. I would like a separate total under each tab for each location.

It can be done. The easiest way would be to re-sort the data for Origin on each tab, then count for each Origin, the same way HC was counted. Would you want data on each sheet sorted by HC or Origin when it's done?

[foxguy]

Kevin;

Try this file Copy of Performance Data Dump2.zip

[rushdenx1]

That's great. When it's done the data on each sheet wants to be sorted by HC. Thanks Kevin

[rushdenx1]

Apologies for this. I have just realised that I need the 'ran', 'on time' and '% on time' data for each DESTINATION aswell. Hopefully this is similar to what's already been done for ORIGIN, so won't cause too much work.

I have included an updated file.

Thanks again and apologies for the extra request. Kevin.

[foxguy]

Kevin;

Sorry, I've been away for a few days. Here's a new file Copy of Performance Data Dump3.xls.
I changed the name of the sub that counts. You can call it as many times as you want.

Please Login or Register  to view this content.

sh is the sheet that is getting counted
"G" is the column you want counted
"AB" is the column where you want the results stored.
iLastColumn is a variable that stores the last column used, so that the FormatFilterSortAndPasteToSheet() can autofit enough columns

Please Login or Register  to view this content.

Just make sure that the last call to Count_Column() is the order that you want the sheet to be left in.

[foxguy]

rushdenx1;

In original macro, Origin was being reported with "ARRIVED ON TIME", not "DEPARTED ON TIME"
I changed it in this file. If it was right then you will need to change the 3rd argument from "J" to "L".

Here's the file Copy of Performance Data Dump4.xls. I changed the arguments for Count_Column()
Because it sorts the data on the sheet, you will need to make sure that the last call is the one that leaves the data sorted the way you want.

Here's an example of how to call it.

Please Login or Register  to view this content.