I need help with 6 worksheet functions!

[nrage21]

I'm a mere mortal so I come to the excel gods for help!... please help
me I have about 9k+ rows of the following:

A........................................B
Mobile Device : 102

Summary
Total time...................13D:0H:0M
Total travel time.........8H:59M
Total distance (miles)....179
Number of stops.............39
Total stop time........... 12D:5H:0M

A...................B.............C...........D...............E..........F.............G
date...........time.........speed....duration..distance..county..vh
id#
1/1/04..1:00 PM(EST)..35.........0H:15M....15..........Wch.......102

I need to extract some of the data in the above workbook and
transferred to another workbook.

This is the 6 tasks I need to accomplish:

1) Either copy/paste the "Number of stops" '39' for vehicle 102 from
the summary box... or....look in column D for any 'not empty' cells in
which column G contains '102' and count them, which should result in
'39' as well.

2) Either copy/paste the "Total distance (miles)" '179' from the
summary box above... or...look in column E for any values in which
column G contains '102' and add them all up. Result should be '179.3'
(the number is rounded in the summary box)

3) copy/paste from the summary box "Total travel time"

4) copy/paste from the summary box "Total stop time"

5) Look in column C for any values = or > than 70 and count them in
which column G contains vehicle '102'.

6) Look in column B for any time between 12:00 AM(EST) and 5:00 AM(EST)
and count them in which column G contains vehicle '102'.

I know this is a lot to ask from a newsgroup... but I have learned in
the past that is better to let your helpers know all the details and
requirements to avoid wasting time coming back for more solutions.

- Larry -
VBA Amateur
Forever Grateful! TIA!

 

[Frank Kabel]

Hi
you may use SUMPRODUCT for all of your tasks. Try
1. =SUMPRODUCT(--(D110000<>""),--(G1:G10000=102))

2. =SUMPRODUCT(--(D110000<>""),--(G1:G10000=102),E1:E10000)

3. / 4. not sure how you calculate these values

5. =SUMPRODUCT(--(D110000<>""),--(G1:G10000=102),--(C1:C10000>=70))

6. Did you mean 5 pm?
=SUMPRODUCT(--(D110000<>""),--(G1:G10000=102),--(B1:B10000>=12/24),--
(B1:B10000<=17/24))

Note: I'm not sure why you have summary and detal information in ONE
worksheet. Consider the following:
- have all your detail in only one sheet in a database like structure
- try using pivot tables for this kind of summary reports on a separate
sheet

 

[nrage21]

Thanks Frank! I kind of knew you were going to be the one to respond..
no surprises... but very grateful thanks!

Yes I meant 5:00 AM(EST)

I guess for 3 and 4 I will need VBA.

- Larry -
VBA Amateu

 

[Frank Kabel]

Hi
if you can explain how you get these values there may be a different
solution

 

[nrage21]

The numbers are generated tru a vehicle GPS tracking system... th
company responsible for this service emails this report on a biweekl
basis in xls format.

I included the 1st 500 rows of the report since i can really attac
large files in this newsgroup. I will be sending the new workbook
want to add the new numbers in.

- Larry

Attachment filename: activity rpt.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51454

 

[nrage21]

Here's the 2nd workbook where i need the data.


- Larry -
VBA Amateu

Attachment filename: transportation summary and detail vehicle report.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=51456

 

[Frank Kabel]

Hi
to be honest: Ask the service company for a report with the raw data
(should be no problem for them). Otherwise you'll need VBA for this

 

[Frank Kabel]

Hi
just as an addition. Try the following formulas:
3. total trave time: enter the array formula (with CTRL+SHIFT+ENTER)
=OFFSET($B$1,MATCH(TRUE,ISNUMBER(FIND("102",$A$1:$A$10000)),0)+3,0)

4. total stop time: enter the array formula (with CTRL+SHIFT+ENTER)
=OFFSET($B$1,MATCH(TRUE,ISNUMBER(FIND("102",$A$1:$A$10000)),0)+6,0)

 

[nrage21]

That's exactly what I told my hard head management team. They asked fo
this kind of report and they're still with their mouths open when th
service company demanded the $$ for this task. So they did the nex
best thing, they dumped it on me, the new guy (5 months in this smal
company).

So I have to make the most of it. I'm practicly against the wall o
this one. And they have no plans to raise the peanuts of a salary
get. So my plan is to establish some solid work before I go into m
boss office and demand a raise.


- Larry -
VBA Amateu

 

[nrage21]

Correction:
Sorry!!!
For task #2 I dont need to sum but I need the function to look for th
largest value in column E... where column G is '102'.


- Larry -
VBA Amateu

 

[Frank Kabel]

Hi
try the array formula
=MAX(IF((D110000<>"")*(G1:G10000=102)),E1:E10000)

 

[Gord Dibben]

Larry

Best to have a contingency plan for when your "demand" is turned down<g>

Sometimes those "hard heads" are exactly that.

Gord Dibben Excel MVP