Earliest/Latest Date Query

[Terry Bennett]

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.

 

[T. Valko]

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)

 

[Max]

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)

 

[Terry Bennett]

Many thanks - I will give it a try

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)

 

[Max]

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.

---

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Many thanks - I will give it a try

 

[Max]

You didn't give Terry the count, Biff (his item 1) <g>

---

 

[T. Valko]

I have resolved 1) using SUMPRODUCT

 

[Max]

Ah, so! Looks like I had a mental block on that OP's line

---

 

[Terry Bennett]

Thanks for the advice Max. I must admit that my previous experiences with
Pivot Tables have not been happy ones so my natural inclination is to look
for an array solution. That said, as you have so kindly set it all out I
will give the PT option a whirl and see how it works-out!

Terry

 

[Max]

Welcome, Terry. Thanks for feeding back here.