Deleting columns & sorting by Type

**rushdenx1** · 05-28-2010, 08:36 AM

I have been using Excel for a number of years, but never Macro's. Can someone give me some help to produce a Macro that will help me sorting some Data. I am using Excel 2002 SP3.
I enclose the relevant file which shows the times of lorry movements to and from locations and whether they depart and arrive on time or late.
The information on the DATA sheet is cut and pasted from another departments document. This is produced daily. The headings and columns stay the same, but the contents varies from day to day. I need to produce stastistics on a weekly basis, so planned to cut and paste the the data each day until the DATA sheet contains all the infomation for the week. I would then like to know how to produce a Macro Button on the sheet which when pressed would do the following:
1/ Delete columns F,G,K,L,N,P,R,T and V.
2/ Sort the remaining columns by TYPE (Column C) and HC (Column B)
3/ All the data for each TYPE (ie B,C,G etc) to be exported to the corresponding worksheet of the same letter.
There are a few more bits needed, but I would rather do things in stages. Any help is really appreciated. Kevin

**Marcol** · 05-28-2010, 08:56 AM

Hi rushdenx1
Please change your thread title, before the moderators get you...

I have your file and will give it a go.

Forum Rules And Guidance
1. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

**Marcol** · 05-28-2010, 02:05 PM

Try this to start with.

To populate sheets B to Z
Run the macro "FilterSortAndPasteToSheet"

To clear sheets B to Z
Run the macro "ClearSheetsNotData"

Sheet("Data") is sorted on C then B, otherwise it remains as you built it.

Hope this helps

**rushdenx1** · 05-28-2010, 03:19 PM

Many thanks for the help. Have noticed two of the columns are not giving the correct info. It's nothing to do with your great work, but a formula in the original data.

1/ I would like Column O to show how early or late the lorry departed. Taking Line 2
(66711/705) as an example, this will be Column E (23:34) against Column M (23:50). So Column O should read 16. Anything above 10 and the cell needs to turn RED.
2/ Likewise Column S to show how early or late the lorry arrived. Taking Line 2 as the example again, this will be Column J (00:17) against Column Q (00:25). So Columdn S shows 8.
If the lorry departs or arrives early then the figure in Column O or S needs to be a negative.
ie -5.

Thanks again for the help. Hope you don't mind trying to sort the above. Kevin.

**Marcol** · 05-29-2010, 06:31 AM

Check this over to see if the new formulae and formatting is what you are after.

I have changed columns O & S to return actual minutes rather than formating a time difference, this makes the conditional formatting a straight forward matter.

There will be errors if Departure/Actual Departure go over midnight, same applies to Arrival.
We can sort that later if the situation exists in reallity.

Cheers

P.S.
I have not checked out if this makes any difference to the code results, I don't think it should, with a bit of luck the formatting should be carried over to the breakdown sheets.

**rushdenx1** · 05-29-2010, 09:19 AM

Thanks Again. The information appears correct in both the main sheet and the separate sheets. One question however. The information that I will be cutting and pasting into the Data sheet each day with not have the O and S columns formatted in the way you have just done. Does this mean that I will need to copy the formula into the top cells of each column and drag it down to the bottom in order to get the correct times or is there a way of automating that aswell. Thanks Kevin.

**Marcol** · 05-29-2010, 07:56 PM

Okay, Try this.

Cut and paste your data to sheet "Data" as you would normally do.

Then run the revised macro renamed "FormatFilterSortAndPasteToSheet"
This new macro will reformat your data to the new rules before sorting and copying.

**rushdenx1** · 05-31-2010, 03:19 PM

I have cut and pasted a weeks worth of data into the enclosed file. The macro has now sorted the data into the individual 'lettered' worksheets. On each sheet under the HC column there will be more than one entry for most ie on the 'C' sheet 4E78, 4M21 etc. These are the same lorry journeys but on different days. What I would like is a Macro which would give me the following:
1/ Number of times each HC has run ie 4E33 = 4
2/ Of that number how many times did it arrive 'on time' (between 0-10 mins of ARR)
3/ What this figure is as a percentage (ie ran 4 times, 'on time' 2 times = 50%)
This information can appear in 3 columns alongside the esisting data on each lettered sheet or a separate sheet for each letter. Whichever is easiest.
Thankyou in anticipation. Kevin.