I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route number
in one column and because that route number is found in that column anywhere
from once to 60 times, return the smallest number in the time column for that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for route
521 and 2000 for the last time the bus picks up passengers because it is the
largest time in the time column for route 521. Is this possible?

=MIN(IF($A$2:$A$1000=521,$B$2:$B$1000))

=MAX(IF($A$2:$A$1000=521,$B$2:$B$1000))

Enter both with Ctrl+Shift+Enter

HTH

"JICDB" wrote:

I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route number
in one column and because that route number is found in that column anywhere
from once to 60 times, return the smallest number in the time column for that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for route
521 and 2000 for the last time the bus picks up passengers because it is the
largest time in the time column for route 521. Is this possible?

On Mon, 30 Jul 2007 07:44:02 -0700, JICDB
wrote:

I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route number
in one column and because that route number is found in that column anywhere
from once to 60 times, return the smallest number in the time column for that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for route
521 and 2000 for the last time the bus picks up passengers because it is the
largest time in the time column for route 521. Is this possible?

Assume your columns are named "Route" and "Time"

E3: Route number of interest

Use the **array** formulas:

=MAX(IF(Route=E3,Time,""))
=MAX(IF(Route=E3,Time,""))

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

Hi

With the data in columns A and B, I entered 521 in cell C2.
The following array formula will give the earliest and latest times
Earliest
{=MIN(IF(A2:A14=C2,B2:B14))}
Latest
{=MAX(IF(A2:A14=C2,B2:B14))}

Array formulae must be Entered or Amended using Control + Shift + Enter
(CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { } for you. Do not
type them yourself.
--
Regards
Roger Govier



"JICDB" wrote in message
...
I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route
number
in one column and because that route number is found in that column
anywhere
from once to 60 times, return the smallest number in the time column for
that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for
route
521 and 2000 for the last time the bus picks up passengers because it is
the
largest time in the time column for route 521. Is this possible?

Another quick alternative is to use a pivot table (PT). In a matter of
seconds, the PT can provide both the required uniques listing of the Routes
and the corresponding Min & Max "Time" values

First, ensure that all the "Time" values are converted to real numbers.
Select an empty cell, then right-click on the Time col Paste special Add
OK.

Then select any cell within the source table
Click Data PivotTable .. Click Next Next.
In step 3 of the wizard, click Layout.
Drag n drop Route within the ROW area.
Drag n drop Time within the DATA area twice, one below the other.
It'll appear as "Sum of Time/Time2". Double click on the 1st, choose "Min"
under Summarize by, change the name/label to say, "1st bus", click OK. Double
click on the 2nd, choose "Max" under Summarize by, change the label to say,
"Last bus", click OK. Click OK Finish.

Hop over to the PT sheet. Just drag n drop Data onto "Total", and you would
have the required results as per sample below (Just ignore or hide away the
bottom "Grand Total" line):

..........Data
Route 1st bus Last bus
521 630 2000
522 530 xxxx
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JICDB" wrote:
I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route number
in one column and because that route number is found in that column anywhere
from once to 60 times, return the smallest number in the time column for that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for route
521 and 2000 for the last time the bus picks up passengers because it is the
largest time in the time column for route 521. Is this possible?

Morn', just housekeeping here <g ..

Line:
Select an empty cell, then right-click on the Time col ..

should have read as:
Copy an empty cell, then right-click on the Time col ..

The step above is not necessary if the Time col already contains real
numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

=MIN(IF(A2:A14=521,B2:B14))
--
David Biddulph

"JICDB" wrote in message
...
I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route
number
in one column and because that route number is found in that column
anywhere
from once to 60 times, return the smallest number in the time column for
that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for
route
521 and 2000 for the last time the bus picks up passengers because it is
the
largest time in the time column for route 521. Is this possible?

You guys are awesome! Thanks. I'll try all of them and see which works
best. Thanks again.

Patti

"David Biddulph" wrote:

=MIN(IF(A2:A14=521,B2:B14))
--
David Biddulph

"JICDB" wrote in message
...
I have a table that contains hundreds of records listing bus route numbers
and the start time of each trip. What I want to do is look up a route
number
in one column and because that route number is found in that column
anywhere
from once to 60 times, return the smallest number in the time column for
that
route. Example"

Route Time
521 0630
521 0735
521 0920
521 1015
521 1120
521 1215
521 1324
521 1420
521 1610
521 1905
521 2000
522 0530
522 0613
etc.

I want the lookup table to help isolate the operating time of the route
extracting the start time of 0630 because it is the smallest time for
route
521 and 2000 for the last time the bus picks up passengers because it is
the
largest time in the time column for route 521. Is this possible?

Hello,

If your data is sorted (as you show it):
Route First Last
521 =INDEX(Source!B:B,MATCH(A2,Source!A:A,)) =LOOKUP(2,1/(A2=Source!
A:A),Source!B:B)
522 =INDEX(Source!B:B,MATCH(A3,Source!A:A,)) =LOOKUP(2,1/(A3=Source!
A:A),Source!B:B)
....

Regards,
Bernd