help with a function or a macro 2

[basictypeuser]

Hi All;

Hope someone could help with either a function or a macro to help speed my data crunch. Here is my example.

The following are each in a column. Bus number, miles traveled, date fueled, qty of fuel. I have alot of data which so far I have broke down into months. I am trying to figure miles per gallon on a monthly basis for each vehicle, then a miles per gallon average for all. So far I am doing this on each vehicle. I have 232 buses to perform this on.

some data examples attached.

Bus Number Accum Mileage Date Quantity
2201 191 2/15/2005 122
2201 191 2/16/2005 118.3
2201 203 2/18/2005 122.5
2201 199 2/18/2005 110.3
2201 89 2/25/2005 77.7
2201 169 2/26/2005 112.8
2201 198 2/27/2005 131.9
2201 191 2/28/2005 135
2202 192 2/15/2005 144.4
2202 196 2/16/2005 150.9
2202 196 2/17/2005 142.3
2202 206 2/18/2005 136.6
2202 205 2/19/2005 146.5
2202 197 2/20/2005 128.5
2202 199 2/21/2005 114.7
2202 46 2/22/2005 43.6


Any help would be appreciated.

 

[RoyVidar]

Assuming this is Excel. Stuff all the buses, mileages... into one sheet. Make one extra column for month, with the formula =month(<DateColumn>). Also, of course, make one column for miles per gallon.

Then take a trip into the wonderfull world of the Data menu, first, find sort - apply sort first on bus, then on date.

Then find subtotals (still data menu).

Then select
"At each change in ": Month
"Use function": Avg
"Add subtotoal to": "Miles per galon"

Keep "Summary below data" selected.

Roy-Vidar

 

[arthurbr]

Hi RoyVidar,
wouldn't it be possible to use Pivot Tables with multiple consolidation ranges ?
That would leave the data oon each sheet without having to reorganize everything?

André

 

[SkipVought]

Multiple consolidation ranges are ver limiting and klunky IMHO.

I'd DEFINITELY first consolidate my data into a single source.

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[byundt]

Assuming that you have one big table (laid out as you have shown), then you can set up a PivotTable to summarize your data and perform your miles/gallon calculations. To do this:
Creating a PivotTable can be somewhat challenging because of the unfamiliar nature of the wizard. To create a PivotTable:
1) Make sure that each column has a heading label (as you have already done), then select any point within your data
2) Open the Data...PivotTable and PivotChart Report menu item
3) Click Finish in the resulting wizard. This will create a PivotTable on a new worksheet.

The tricky part come next. You will be presented with a blank PivotTable with big labels for the type of data required in each section. You need to drag named fields from the "PivotTable field list" dialog onto the PivotTable.
4) Drag the "Bus Number" field label into the "Drag Row Items Here" field
5) Drag the "Date" field label into the "Drag Row Items Here" field, to the right of the "Bus Number"
6) Drag the "Mileage" field label into the "Drag Data Items Here" field
7) Drag the "Quantity" field label on top of the "Sum of Accum Mileage" field label
8) Right-click any cell containing a date, then choose "Group and Show Detail...Group" from the resulting popup menu. Choose by "Months".
9) Right-click any cell containing a bus number, then choose "Field Settings".
10) In the resulting dialog, choose "None" as the option for Subtotals
11) Click in the column to the right of the PivotTable, then enter a formula for the miles/gallon like:
=C5/D5
Copy this formula down.

The final results should resemble:
Data
BusNumber Date Sum of Accum Mileage Sum of Quantity Miles/gallon
2201 Feb 1431 930.5 1.537882859
2202 Feb 1437 1007.5 1.42630273
Grand Total 2868 1938

Brad

 

[basictypeuser]

Brad,

I have tried several of the methods other suggested in this thread. So far yours has been easier. I still can't seem to get the outcome to look the same as you indicated. I guess I am missing a step. For some reason I cannot get the sum of miles and qty in there own column. My results look like this.

2101 Jan Sum of Accum Mileage 5098
Sum of Quantity 3303.7
Feb Sum of Accum Mileage 3921
Sum of Quantity 2394.8
Mar Sum of Accum Mileage 3920
Sum of Quantity 2671.1
Apr Sum of Accum Mileage 4173
Sum of Quantity 3215.3
May Sum of Accum Mileage 4297
Sum of Quantity 2935.6
Jun Sum of Accum Mileage 4050
Sum of Quantity 2723.2
Jul Sum of Accum Mileage 3818
Sum of Quantity 2241.1
Aug Sum of Accum Mileage 759
Sum of Quantity 506.2
Sep Sum of Accum Mileage 3155
Sum of Quantity 2157.6
Oct Sum of Accum Mileage 4832
Sum of Quantity 3259.7
Nov Sum of Accum Mileage 4257
Sum of Quantity 2840
Dec Sum of Accum Mileage 4614
Sum of Quantity 2748.9


Thanks,

 

[byundt]

If you get this situation, left-click on the field label that says "Data" and drag it to the right one column and drop it on top of "Total". This will array your Accum Mileage and Quantity in separate columns.

Brad

 

[byundt]

The other trick you need to remember is when creating the formula for gas mileage, you must type in the cell addresses--don't click on the cells you want to refer to. If you click on the cells, you will get a GetPivotData function reference that won't copy down the way you need.

 

[basictypeuser]

Brad,

This works great!!! Now that I have something I can use. How well does this work for partial months. For instance my data started on 2/15/05 and ended on 2/16/06. How do I view just the partial month data. Also, if I am viewing the data by a specific month, can I sort to show the bus with the highest and lowest monthly mpg? As you can see I work for a bus company, and have been tasked with this project. Your help is very appreciated!!!!

 

[byundt]

Partial months are no problem--it will be treated just like a month with less data than other months. If you look at the choices in step 8 of my original instructions, you will see that you can choose to group by days, months, quarters and years.

If you want to sort the data by the miles/gallon, then the PivotTable is going to get in the way. The easiest way to deal with this is to:
1) Copy the PivotTable
2) Edit...Paste Special...Values (possibly in some other location)
3) Select the cell range containing bus numbers
4) Run the following macro (install it on a regular module sheet):

Sub Infill()
'Copies the value from the row above into blank cells.
On Error Resume Next
With Range(ActiveCell, Cells(65536, ActiveCell.Column).End(xlUp)) 'Start with active cell, and continue through bottom of data
'With Intersect(ActiveCell.EntireColumn, ActiveCell.CurrentRegion) 'Different way of setting range which works better in some cases
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" 'Blank cells set equal to value from row above
'.Formula = .Value 'Optional: Replace the formulas with the values returned by the formulas
End With
On Error GoTo 0
End Sub

5) Select the rows containing header labels plus your data
6) Open the Data...Sort menu item
7) Make sure the option is chosen for "My data range contains header row", then choose to sort by "Miles/gallon"

Brad

 

[byundt]

Note that steps 3 and 4 in previous comment are needed only if you are looking at the data for more than one month. If you have filtered to show one month only, then you may omit those steps.

Brad

 

[basictypeuser]

Brad,

This is now a bit more complicating. Until I can get the time, I will work with I have. Thanks for help. Very much appreciated!!!!

 

[arthurbr]

Hi Basic,

why not show Brad your appreciation with a star ?
Cheers

 

[basictypeuser]

Arthurbr,

I feel real dumb, I tried to do that yesterday, unfortunately did not know how to add them, that is why I thanked him as graciously as possible!!! Could you help with the Star?? This way in the future I can give the appropriate appreciation.

Thanks,

 

[byundt]

There's a link at the bottom of each comment for the Star. When you click it, a star should appear at the top of every comment by that person in this question.

By the way, if you want to try sorting by miles/gallon, the macro is required by step 4. If you use the pivot dropdown to select data for only one month, then you won't need steps 3 or 4--or the macro. As a result, it will probably take you well under a minute to do the sort (steps 1, 2, 5, 6, 7).

Brad