<<Extracting the desired data with an formula>>

[vinu]

Hi,

Pls help me...

I have a data of 6 vendors for 30 days.

I need to extract total run time and total KM of each vehicle on day
basis.

i.e. for 1246, total run time - 23:44-18:00 & the total KM - 84+28.

But the problem is the data was so big and the dead line was EOD..!!

Day VendorName VehicleNo StartTime EndTime KM
1 ATL 1246 18:00 20:43 84
1 ATL 1246 22:38 23:44 28
1 ATL 1558 12:30 16:20 74
1 ATL 1815 4:27 6:31 56
1 ATL 1815 6:00 7:00 67
1 ATL 1815 7:00 8:12 45
1 ATL 1815 7:30 8:00 14
1 ATL 1815 12:55 16:26 33
1 ATL 7915 16:00 17:30 70
1 ATL 7915 20:30 21:15 38
1 ATL 7915 22:30 23:30 47
1 ATL 8037 1:00 1:57 30
1 ATL 8037 5:30 6:10 37
1 ATL 8037 7:00 8:30 68
1 ATL 9583 0:00 0:30 43
1 ATL 9583 3:45 4:32 26
1 Baa 0469 4:30 6:40 54
1 Baa 469 9:00 11:45 71
1 Baa 469 13:46 15:20 32
1 Baa 469 15:30 16:20 34
1 Baa 469 17:08 18:20 29
1 Baa 469 18:30 21:28 54
1 Baa 469 21:30 23:15 29
1 Baa 469 23:00 0:45 71
1 Baa 1195 5:30 8:25 74

 

[Ron Rosenfeld]

Hi,

Pls help me...

I have a data of 6 vendors for 30 days.

I need to extract total run time and total KM of each vehicle on day
basis.

i.e. for 1246, total run time - 23:44-18:00 & the total KM - 84+28.

But the problem is the data was so big and the dead line was EOD..!!

Day VendorName VehicleNo StartTime EndTime KM
1 ATL 1246 18:00 20:43 84
1 ATL 1246 22:38 23:44 28
1 ATL 1558 12:30 16:20 74
1 ATL 1815 4:27 6:31 56
1 ATL 1815 6:00 7:00 67
1 ATL 1815 7:00 8:12 45
1 ATL 1815 7:30 8:00 14
1 ATL 1815 12:55 16:26 33
1 ATL 7915 16:00 17:30 70
1 ATL 7915 20:30 21:15 38
1 ATL 7915 22:30 23:30 47
1 ATL 8037 1:00 1:57 30
1 ATL 8037 5:30 6:10 37
1 ATL 8037 7:00 8:30 68
1 ATL 9583 0:00 0:30 43
1 ATL 9583 3:45 4:32 26
1 Baa 0469 4:30 6:40 54
1 Baa 469 9:00 11:45 71
1 Baa 469 13:46 15:20 32
1 Baa 469 15:30 16:20 34
1 Baa 469 17:08 18:20 29
1 Baa 469 18:30 21:28 54
1 Baa 469 21:30 23:15 29
1 Baa 469 23:00 0:45 71
1 Baa 1195 5:30 8:25 74

Use a Pivot Table

You can make it easier by adding a column called RunTime.

If your labels start in A1, and your Data starts in A2, then

G1: RunTime
G2: =E2+(D2>E2)-D2

and fill down as far as needed.

Then, Insert/Pivot Table

Drag VehicleNo to Rows
Drag RunTime to Data or Value area
Drag Km to Data (or Value) area

Make sure that RunTime and Km are showing "SUM" rather than "COUNT". If not,
right click them and change the "Value Field Settings" accordingly.

Format the RunTime as [h]:mm

Format the table so it is attractive.

Here's what I got with your data:

Values
VehNo Run Time KM
469 14:59 374
1195 2:55 74
1246 3:49 112
1558 3:50 74
1815 8:17 215
7915 3:15 155
8037 3:07 135
9583 1:17 69
Total 41:29 1208
--ron

 

[vinu]

Hi Ron,

Thanks for the help.

but still I have couple of ?'s with me,

1. I can extract Total KM & Total Trips. But I can't get the "total
runtime" of each day..!!
Also the pivot will end up like below
4089 Count of Run time 1
Sum of KM 71
4128 Count of Run time 4
Sum of KM 168
4258 Count of Run time 1
Sum of KM 95


Were even if i change count into SUM icant change the format as
[h]:mm for the runtime seperatly


2. Is there any difference between [h]:mm & h:mm..?!


Regards,
Vinod

 

[Dave Peterson]

Drag the run time button to the data area a second time.

If it says "count of", then double click on it and choose Sum.

Excel will use Sum only if each entry in that field is numeric (a real time).
If you have text or even empty cells, you'll see "Count of".

Hi Ron,

Thanks for the help.

but still I have couple of ?'s with me,

1. I can extract Total KM & Total Trips. But I can't get the "total
runtime" of each day..!!
Also the pivot will end up like below
4089 Count of Run time 1
Sum of KM 71
4128 Count of Run time 4
Sum of KM 168
4258 Count of Run time 1
Sum of KM 95

Were even if i change count into SUM icant change the format as
[h]:mm for the runtime seperatly

2. Is there any difference between [h]:mm & h:mm..?!

Regards,
Vinod

 

[vinu]

Dave,

Still I cant get the reselt that Ron have provided above.

Km has to be next to the run time but I'm getting it below run time.

Regards,

Vinod Kumar K

 

[Dave Peterson]

Take a look at Debra Dalgleish's site:
http://contextures.com/xlPivot02.html
and for a video:
http://contextures.com/xlVideo001.html

After you get the pivottable looking exactly the way you want, save your
workbook--maybe even save a copy as a new name.

Then you can see how powerful those pivottables are by dragging those field
buttons around the pivottable.

You'll be surprised at how powerful those pivottables are. And all by just
dragging those field buttons around.

 

[Ron Rosenfeld]

Hi Ron,

Thanks for the help.

but still I have couple of ?'s with me,

1. I can extract Total KM & Total Trips. But I can't get the "total
runtime" of each day..!!

You did not mention that you wanted the "total runtime" of each day. You wrote
that you wanted the total runtime for each vehicle. And I see no place in your
data where you have information regarding a "day".

If the entire table is for one day, and you want to see a total line, as I have
in my example, showing a Grand Total line is one of the pivot table options,
usually accessible from the right-click menu.

Also the pivot will end up like below
4089 Count of Run time 1
Sum of KM 71
4128 Count of Run time 4
Sum of KM 168
4258 Count of Run time 1
Sum of KM 95


Were even if i change count into SUM icant change the format as
[h]:mm for the runtime seperatly

What exact formula are you using for the RunTime column?

What do you mean "separately"? If you select the column containing the
runtime, you should be able to change it.

2. Is there any difference between [h]:mm & h:mm..?!

[h]:mm allows display of more than 24 hrs.
h:mm will display 25 hrs as 1 hr.

Be sure you are following the directions precisely as I laid out. For example,
you do NOT want to drag KM and RunTime to the Columns area; only to the Data or
Value area.

Regards,
Vinod

--ron

 

[vinu]

Dave,
Thanks for the link.
Finding little difficulty click in to that link. I'm sure your
suggestions will never shortfall to my expectation.

Ron,
In the starting I only wanted total run time & Total KM only,

i.e. for 1246, total run time - 23:44-18:00 & the total KM - 84+28.

After your suggestion,
I made a separate column for runtime.

Your suggestions where great help & I only fail on aligning the pivot
rightfully.
I think Dave's link will help me on this.

I have a data of 6 vendors for "30 days".
I need to extract "total run time" and total KM of "each vehicle" on "day

basis".

I might not communicated properly in my first post, sorry

Regards,
Vinod Kumar

 

[Ron Rosenfeld]

Dave,
Thanks for the link.
Finding little difficulty click in to that link. I'm sure your
suggestions will never shortfall to my expectation.

Ron,
In the starting I only wanted total run time & Total KM only,


After your suggestion,

I made a separate column for runtime.

Your suggestions where great help & I only fail on aligning the pivot
rightfully.
I think Dave's link will help me on this.

basis".

I might not communicated properly in my first post, sorry

Regards,
Vinod Kumar

I don't see how you wound up with KM under RunTime in your Pivot table. They
should have been side by side.

Also, with regard to doing a "Daily" count, I missed the First Column in your
example labeled "Days"

Depending on how you want your results to look (horizontal vs vertical), you
could either drag Days to the Column Label area, Or you could drag Days to the
row label area and ensure that it is ABOVE the Vehicle Numbers.

The first might look like:

Column Labels
1 2 Total KM Total
RunTimes
Vehicles KM RunTimes KM RunTimes
469 125 4:55 125 4:55 250 9:50
1246 112 3:49 112 3:49 224 7:38
1558 74 3:50 74 3:50 148 7:40
1815 215 8:17 215 8:17 430 16:34
7915 155 3:15 155 3:15 310 6:30
8037 135 3:07 135 3:07 270 6:14
9583 69 1:17 69 1:17 138 2:34
Grand Total 885 28:30 885 28:30 1770 57:00


And the second might look like:
Values
Row Labels Sum of KM Sum of RunTime
1 885 28:30
469 125 4:55
1246 112 3:49
1558 74 3:50
1815 215 8:17
7915 155 3:15
8037 135 3:07
9583 69 1:17

2 885 28:30
469 125 4:55
1246 112 3:49
1558 74 3:50
1815 215 8:17
7915 155 3:15
8037 135 3:07
9583 69 1:17
Grand Total 1770 57:00


--ron

 

[vinu]

Hi Ron / Dave,

Tons of thanks to you two...

I got it at last..

really the link that Dave gave was the perfect one to finish it up.

EXCELlent..

Thanks,

Vinod

 

[Ron Rosenfeld]

Hi Ron / Dave,

Tons of thanks to you two...

I got it at last..

really the link that Dave gave was the perfect one to finish it up.

EXCELlent..

Thanks,

Vinod

Glad to help. Thanks for the feedback.
--ron