Lookup for smallest number?

[JICDB]

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?

 

[Ron Rosenfeld]

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

 

[Roger Govier]

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.

 

[Max]

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

 

[David Biddulph]

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

 

[JICDB]

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

Patti

 

[Max]

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

 

[Bernd P]

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