Highest number in two colums

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thermometer98 - 28 Feb 2006 19:22 GMT

I have two columns. Column A is mileage. Column B is my trip number.
Sometimes I traveled several different miles on the same trip number.
How can I easily extract a trip number and the highest mileage traveled
on that particular trip? (like trip 5 and 251 miles)

Miles Trip
10    1
131    2
166    3
64    4
48    4
251    5
172    5
172    5
151    5
50    6
28    7
88    8
86    8
5    8
5    8
48    9
32    9
32    9
63    10
166    11
44    11
44    11

Reply to this Message

Biff - 28 Feb 2006 19:30 GMT

Hi!

Try this:

D2 = trip number = 5

Formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(B2:B23=D2,A2:A23))

Biff

>I have two columns. Column A is mileage. Column B is my trip number.
> Sometimes I traveled several different miles on the same trip number.
[quoted text clipped - 24 lines]
> 44 11
> 44 11

Reply to this Message

Chip Pearson - 28 Feb 2006 19:35 GMT

If Miles are in column A, you can get the maximum value with

=MAX(A1:A10)

If Trip is in column B, you can get the trip number corresponding
to the max value in A with

=INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,0))

Adjust all the ranges to reflect your data.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

>I have two columns. Column A is mileage. Column B is my trip
>number.
[quoted text clipped - 27 lines]
> 44 11
> 44 11

Reply to this Message

davesexcel - 28 Feb 2006 19:42 GMT

=MAX(VLOOKUP(D14,A8:B29,2,FALSE))
i used A8:B29 as the range and reversed the order so that trip was th
first column
cell D14 you input the trip number
cell E14 you input the above formula
adjust the ranges as you need t

Reply to this Message

thermometer98 - 28 Feb 2006 19:52 GMT

I'm a bit confused, but I guess it's my fault for not saying that the
data is 200 rows long. I'm not sure how the two formulas suggested
would work. Sorry about that.

Reply to this Message

Chip Pearson - 28 Feb 2006 19:54 GMT

Just change the range references in the example formulas to the
range of cells that contain your data.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> I'm a bit confused, but I guess it's my fault for not saying
> that the
> data is 200 rows long. I'm not sure how the two formulas
> suggested
> would work. Sorry about that.

Reply to this Message

thermometer98 - 28 Feb 2006 20:00 GMT

Sure, but the problem is that I have many trips and I need to pull the
highest mileage from each one. I would like to end up with a table
like:
1 10
2 131
3 166
4 64
etc.
200 99

Reply to this Message

Dave Peterson - 28 Feb 2006 20:10 GMT

Sounds like a perfect opportunity to learn about pivottables.

Add headers to your data (Miles and Trip) if you don't have them.
select A1:Bxx
date|pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the Trip button to the row field
drag the miles button to the data field.

double click on that "sum of miles" button and choose Max.

And finish up the wizard.

> Sure, but the problem is that I have many trips and I need to pull the
> highest mileage from each one. I would like to end up with a table
[quoted text clipped - 5 lines]
> etc.
> 200 99

Signature

Dave Peterson

Reply to this Message

thermometer98 - 28 Feb 2006 20:19 GMT

Yup. Pivot worked great. Thanks.
And thanks to all for the suggestions.
Sorry I wasn't very clear.

Reply to this Message