I have a collection of data in a spreadsheet that is 50,000 + lines
long for truck mileage sorted by vehicle number.
The column left of the truck number is the odometer reading when it was
fueled.
I need a formula or function that will find the first entry and last
entry for each vehicle and subtract the last mileage from the first to
give me total mileage for each vehicle.
The total lines for each vehicle varies from vehicle to vehicle.
Hope I gave enough info to figure this out.
Thanks, Mark

Maybe a pivot table?:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the TruckNUmber field here

DATA:
Drag the Odometer field here
dbl-click it and set it to Min

Drag the Odometer field here, again
dbl-click it and set it to Max

Click [OK]
Select where you want the Pivot Table...and click the [Finish] button

That will list each truck and the Min and Max odometer readings...but, in
the wrong configuration (stacked instead of side by side).

Click and hold on the DATA heading
Drag it on top of the Total heading and release.

Now the Min and Max are side by side

All that's left to do is put formulas to the right that subtract the Min
from the Max.
If Excel keeps creating the GetPivotData function when you attempt to build
the formula....you can disable that feature by following the instructions at
Debra Dalgleish's website:
http://www.contextures.com/xlPivot06.html#GetPivotData

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"markstro" wrote:

> I have a collection of data in a spreadsheet that is 50,000 + lines
> long for truck mileage sorted by vehicle number.
> The column left of the truck number is the odometer reading when it was
> fueled.
> I need a formula or function that will find the first entry and last
> entry for each vehicle and subtract the last mileage from the first to
> give me total mileage for each vehicle.
> The total lines for each vehicle varies from vehicle to vehicle.
> Hope I gave enough info to figure this out.
> Thanks, Mark
>
>

Hi!

Try this:

Column A = odometer reading
Column B = vehicle number

=LOOKUP(2,1/(B$1:B$50000=Vehicle_Number),A$1:A$50000)-INDEX(A$1:A$50000,MATCH(Vehicle_Number,B$1:B$50000,0))

Biff

"markstro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a collection of data in a spreadsheet that is 50,000 + lines
> long for truck mileage sorted by vehicle number.
> The column left of the truck number is the odometer reading when it was
> fueled.
> I need a formula or function that will find the first entry and last
> entry for each vehicle and subtract the last mileage from the first to
> give me total mileage for each vehicle.
> The total lines for each vehicle varies from vehicle to vehicle.
> Hope I gave enough info to figure this out.
> Thanks, Mark
>

>subtract the last mileage from the first

Hmmm.....

My formula does the opposite!

Just flip it around so that it's:

=INDEX(........)-LOOKUP(........)

Biff

"Biff" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi!
>
> Try this:
>
> Column A = odometer reading
> Column B = vehicle number
>
> =LOOKUP(2,1/(B$1:B$50000=Vehicle_Number),A$1:A$50000)-INDEX(A$1:A$50000,MATCH(Vehicle_Number,B$1:B$50000,0))
>
> Biff
>
> "markstro" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I have a collection of data in a spreadsheet that is 50,000 + lines
>> long for truck mileage sorted by vehicle number.
>> The column left of the truck number is the odometer reading when it was
>> fueled.
>> I need a formula or function that will find the first entry and last
>> entry for each vehicle and subtract the last mileage from the first to
>> give me total mileage for each vehicle.
>> The total lines for each vehicle varies from vehicle to vehicle.
>> Hope I gave enough info to figure this out.
>> Thanks, Mark
>>
>
>

I am trying to work out the pivot table suggestion, however, I tried
yours as well and have a few questions.
I get an #N/A when I type your formula as shown.
Vehicle number is in column E titled TRUCK, odometer is column D
labeled ODO
What does the 2,1/ mean at the beginning of your formula?
What should the result look like when I get the bugs out of the
formula?
Thanks for your help
Mark
Biff wrote:
> Hi!
>
> Try this:
>
> Column A = odometer reading
> Column B = vehicle number
>
> =LOOKUP(2,1/(B$1:B$50000=Vehicle_Number),A$1:A$50000)-INDEX(A$1:A$50000,MATCH(Vehicle_Number,B$1:B$50000,0))
>
> Biff
>
> "markstro" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have a collection of data in a spreadsheet that is 50,000 + lines
> > long for truck mileage sorted by vehicle number.
> > The column left of the truck number is the odometer reading when it was
> > fueled.
> > I need a formula or function that will find the first entry and last
> > entry for each vehicle and subtract the last mileage from the first to
> > give me total mileage for each vehicle.
> > The total lines for each vehicle varies from vehicle to vehicle.
> > Hope I gave enough info to figure this out.
> > Thanks, Mark
> >

you should find some junior database developer to help you with this.

Excel doesn't work for managing DATA. a DATABASE would be a better
choice.

I would put a posting on Craigslist to look for an Access Developer and
you'll probably get the report you need in just a few minutes.


-Aaron


markstro wrote:
> I am trying to work out the pivot table suggestion, however, I tried
> yours as well and have a few questions.
> I get an #N/A when I type your formula as shown.
> Vehicle number is in column E titled TRUCK, odometer is column D
> labeled ODO
> What does the 2,1/ mean at the beginning of your formula?
> What should the result look like when I get the bugs out of the
> formula?
> Thanks for your help
> Mark
> Biff wrote:
> > Hi!
> >
> > Try this:
> >
> > Column A = odometer reading
> > Column B = vehicle number
> >
> > =LOOKUP(2,1/(B$1:B$50000=Vehicle_Number),A$1:A$50000)-INDEX(A$1:A$50000,MATCH(Vehicle_Number,B$1:B$50000,0))
> >
> > Biff
> >
> > "markstro" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >I have a collection of data in a spreadsheet that is 50,000 + lines
> > > long for truck mileage sorted by vehicle number.
> > > The column left of the truck number is the odometer reading when it was
> > > fueled.
> > > I need a formula or function that will find the first entry and last
> > > entry for each vehicle and subtract the last mileage from the first to
> > > give me total mileage for each vehicle.
> > > The total lines for each vehicle varies from vehicle to vehicle.
> > > Hope I gave enough info to figure this out.
> > > Thanks, Mark
> > >

The pivot table worked out just fine, thanks for the tip though.
(E-Mail Removed) wrote:
> you should find some junior database developer to help you with this.
>
> Excel doesn't work for managing DATA. a DATABASE would be a better
> choice.
>
> I would put a posting on Craigslist to look for an Access Developer and
> you'll probably get the report you need in just a few minutes.
>
>
> -Aaron
>
>
> markstro wrote:
> > I am trying to work out the pivot table suggestion, however, I tried
> > yours as well and have a few questions.
> > I get an #N/A when I type your formula as shown.
> > Vehicle number is in column E titled TRUCK, odometer is column D
> > labeled ODO
> > What does the 2,1/ mean at the beginning of your formula?
> > What should the result look like when I get the bugs out of the
> > formula?
> > Thanks for your help
> > Mark
> > Biff wrote:
> > > Hi!
> > >
> > > Try this:
> > >
> > > Column A = odometer reading
> > > Column B = vehicle number
> > >
> > > =LOOKUP(2,1/(B$1:B$50000=Vehicle_Number),A$1:A$50000)-INDEX(A$1:A$50000,MATCH(Vehicle_Number,B$1:B$50000,0))
> > >
> > > Biff
> > >
> > > "markstro" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > >I have a collection of data in a spreadsheet that is 50,000 + lines
> > > > long for truck mileage sorted by vehicle number.
> > > > The column left of the truck number is the odometer reading when it was
> > > > fueled.
> > > > I need a formula or function that will find the first entry and last
> > > > entry for each vehicle and subtract the last mileage from the first to
> > > > give me total mileage for each vehicle.
> > > > The total lines for each vehicle varies from vehicle to vehicle.
> > > > Hope I gave enough info to figure this out.
> > > > Thanks, Mark
> > > >

Thanks Ron, it worked just fine, you even got me over a hurdle
regarding pivot tables I have been struggling with (self teaching).
Thanks again, Mark
Ron Coderre wrote:
> Maybe a pivot table?:
>
> <Data><Pivot Table>
> Use: Excel
> Select your data
> Click the [Layout] button
>
> ROW: Drag the TruckNUmber field here
>
> DATA:
> Drag the Odometer field here
> dbl-click it and set it to Min
>
> Drag the Odometer field here, again
> dbl-click it and set it to Max
>
> Click [OK]
> Select where you want the Pivot Table...and click the [Finish] button
>
> That will list each truck and the Min and Max odometer readings...but, in
> the wrong configuration (stacked instead of side by side).
>
> Click and hold on the DATA heading
> Drag it on top of the Total heading and release.
>
> Now the Min and Max are side by side
>
> All that's left to do is put formulas to the right that subtract the Min
> from the Max.
> If Excel keeps creating the GetPivotData function when you attempt to build
> the formula....you can disable that feature by following the instructions at
> Debra Dalgleish's website:
> http://www.contextures.com/xlPivot06.html#GetPivotData
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "markstro" wrote:
>
> > I have a collection of data in a spreadsheet that is 50,000 + lines
> > long for truck mileage sorted by vehicle number.
> > The column left of the truck number is the odometer reading when it was
> > fueled.
> > I need a formula or function that will find the first entry and last
> > entry for each vehicle and subtract the last mileage from the first to
> > give me total mileage for each vehicle.
> > The total lines for each vehicle varies from vehicle to vehicle.
> > Hope I gave enough info to figure this out.
> > Thanks, Mark
> >
> >