Highest number in two colums

  • Thread starter Thread starter thermometer98
  • Start date Start date
T

thermometer98

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
 
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
 
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.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
=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
 
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.
 
Just change the range references in the example formulas to the
range of cells that contain your data.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
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
 
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.
 
Yup. Pivot worked great. Thanks.
And thanks to all for the suggestions.
Sorry I wasn't very clear.
 
Back
Top