I'm sure there must be a simple way of doing this ...

I have 2 columns of data:

A1:A2000 contains data of vehicles used to transport goods
B1:B2000 contains the dates on which each vehicle was used over the space of
several years

There are many duplicate entries in each column (ie; one particular vehicle
will appear many times as will any given date).

I simply need to set-up a summary that shows:

1) The total number of times each vehicle was used
2) The earliest date each vehicle was used
3) The latest date each vehicle was used

I have resolved 1) using SUMPRODUCT but can't at the moment work-out the
answer to 2) and 3). VLOOKUP comes to mind but maybe there is another
SUMPRODUCT solution?

Thanks for any help.

Try these array formulas**:

A1:A2000 = vehicle ID
B1:B200 = dates

D1 = some vehicle ID

Earliest date:

=MIN(IF(A1:A2000=D1,B1:B2000))

Latest date:

=MAX(IF(A1:A2000=D1,B1:B2000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have 2 columns of data:

A1:A2000 contains data of vehicles used to transport goods
B1:B2000 contains the dates on which each vehicle was used over the space
of several years

There are many duplicate entries in each column (ie; one particular
vehicle will appear many times as will any given date).

I simply need to set-up a summary that shows:

1) The total number of times each vehicle was used
2) The earliest date each vehicle was used
3) The latest date each vehicle was used

I have resolved 1) using SUMPRODUCT but can't at the moment work-out the
answer to 2) and 3). VLOOKUP comes to mind but maybe there is another
SUMPRODUCT solution?

Thanks for any help.

I'm sure there must be a simple way of doing this ...

A pivot table is a fast n easy way

Assuming your dates are real dates in col B,
here's the steps in xl2003 to set it up:

Insert a new top row, put in labels in A1:B1, eg: Veh, Date
Select any cell within the data, click Data PivotTable ...
Click Next Next

In step 3 of the wizard, click Layout
Drag n drop Veh within the ROW area

Drag n drop Date 3 times within the DATA area
Leave the 1st one as "Count of Date"

Double click on the 2nd, select "Min" under Summarize by
Click Number Date (choose date format) OK OK

Double click on the 3rd, select "Max" under Summarize by
Click Number Date (choose date format) OK OK
Click OK Finish

Go to the pivot sheet
Drag n drop "Data" over "Total",
and you'll get the required results:
- A unique listing of vehicles in 1st col, & next to it
1) The total number of times each vehicle was used (Count of dates)
2) The earliest date each vehicle was used (Min of date)
3) The latest date each vehicle was used (Max of date)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have 2 columns of data:

A1:A2000 contains data of vehicles used to transport goods
B1:B2000 contains the dates on which each vehicle was used over the space
of several years

There are many duplicate entries in each column (ie; one particular
vehicle will appear many times as will any given date).

I simply need to set-up a summary that shows:

1) The total number of times each vehicle was used
2) The earliest date each vehicle was used
3) The latest date each vehicle was used

I have resolved 1) using SUMPRODUCT but can't at the moment work-out the
answer to 2) and 3). VLOOKUP comes to mind but maybe there is another
SUMPRODUCT solution?

Thanks for any help.

Many thanks - I will give it a try

"T. Valko" wrote in message
...
Try these array formulas**:

A1:A2000 = vehicle ID
B1:B200 = dates

D1 = some vehicle ID

Earliest date:

=MIN(IF(A1:A2000=D1,B1:B2000))

Latest date:

=MAX(IF(A1:A2000=D1,B1:B2000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have 2 columns of data:

A1:A2000 contains data of vehicles used to transport goods
B1:B2000 contains the dates on which each vehicle was used over the space
of several years

There are many duplicate entries in each column (ie; one particular
vehicle will appear many times as will any given date).

I simply need to set-up a summary that shows:

1) The total number of times each vehicle was used
2) The earliest date each vehicle was used
3) The latest date each vehicle was used

I have resolved 1) using SUMPRODUCT but can't at the moment work-out the
answer to 2) and 3). VLOOKUP comes to mind but maybe there is another
SUMPRODUCT solution?

Thanks for any help.

Terry,

Your preference of course, but perhaps, just as an experiment, see whether
you get the same results using the pivot* table approach
outlined in my response

What I find that the pivot also does so easily, natively, as part of its
output, is to dredge out the all important uniques listing of vehicles (or
whatever) in the 1st col

*I'm not saying its better than the arrays that Biff suggested. It took me
longer to outline the steps than it will take for you to execute it
(it'll take a couple of seconds at the most to create the pivot)

Don't just pass up the pivot approach, give it a try, see what it can do for
you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You're welcome!

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
Many thanks - I will give it a try

"T. Valko" wrote in message
...
Try these array formulas**:

A1:A2000 = vehicle ID
B1:B200 = dates

D1 = some vehicle ID

Earliest date:

=MIN(IF(A1:A2000=D1,B1:B2000))

Latest date:

=MAX(IF(A1:A2000=D1,B1:B2000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have 2 columns of data:

A1:A2000 contains data of vehicles used to transport goods
B1:B2000 contains the dates on which each vehicle was used over the
space of several years

There are many duplicate entries in each column (ie; one particular
vehicle will appear many times as will any given date).

I simply need to set-up a summary that shows:

1) The total number of times each vehicle was used
2) The earliest date each vehicle was used
3) The latest date each vehicle was used

I have resolved 1) using SUMPRODUCT but can't at the moment work-out the
answer to 2) and 3). VLOOKUP comes to mind but maybe there is another
SUMPRODUCT solution?

Thanks for any help.

You didn't give Terry the count, Biff (his item 1) <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I have resolved 1) using SUMPRODUCT


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
You didn't give Terry the count, Biff (his item 1) <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"T. Valko" wrote in message
...
I have resolved 1) using SUMPRODUCT

Ah, so! Looks like I had a mental block on that OP's line
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---