From range and criteria select highest value
 

Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a criteria.

Thanks

From range and criteria select highest value
 

Source data is assumed in A1:B6

Values listed in D1 down

In E1, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(A$1:A$6=D1,B$1:B$6))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sunnyskies" wrote:
Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a criteria.

Thanks

From range and criteria select highest value
 

With your DB in the range A2:B7...

Unique unit codes in the range E2:E4...

Enter this array formula** in F2 and copy down to F4:

=MAX(IF(A$2:A$7=E2,B$2:B$7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a
criteria.

Thanks

From range and criteria select highest value
 

Hi TV,

Can this not be done without an aray?

Other users need to use the document and they are not savy.

"T. Valko" wrote:

With your DB in the range A2:B7...

Unique unit codes in the range E2:E4...

Enter this array formula** in F2 and copy down to F4:

=MAX(IF(A$2:A$7=E2,B$2:B$7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a
criteria.

Thanks

From range and criteria select highest value
 

Can this not be done without an aray?

An alternative is to create a pivot table, with col A's header placed in ROW
and col B's header in DATA (set to summarize by Max). The pivot will produce
both the unique listing of items (from col A) and the corresponding maximums
adjacent to it
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

From range and criteria select highest value
 

Hi Max,

Thought about that as well, but I want to keep the file size as small as
possible.
So was hoping for a formula.

Thanks

"Max" wrote:

Can this not be done without an aray?

An alternative is to create a pivot table, with col A's header placed in ROW
and col B's header in DATA (set to summarize by Max). The pivot will produce
both the unique listing of items (from col A) and the corresponding maximums
adjacent to it
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

From range and criteria select highest value
 

Can this not be done without an aray?

Try this:

=SUMPRODUCT(MAX((A$2:A$7=E2)*B$2:B$7))

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hi TV,

Can this not be done without an aray?

Other users need to use the document and they are not savy.

"T. Valko" wrote:

With your DB in the range A2:B7...

Unique unit codes in the range E2:E4...

Enter this array formula** in F2 and copy down to F4:

=MAX(IF(A$2:A$7=E2,B$2:B$7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a
criteria.

Thanks

From range and criteria select highest value
 

Hi TV,

Excellent.

Suggest you give yourself a pat on the back.

Thanks

"T. Valko" wrote:

Can this not be done without an aray?

Try this:

=SUMPRODUCT(MAX((A$2:A$7=E2)*B$2:B$7))

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hi TV,

Can this not be done without an aray?

Other users need to use the document and they are not savy.

"T. Valko" wrote:

With your DB in the range A2:B7...

Unique unit codes in the range E2:E4...

Enter this array formula** in F2 and copy down to F4:

=MAX(IF(A$2:A$7=E2,B$2:B$7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a
criteria.

Thanks

From range and criteria select highest value
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hi TV,

Excellent.

Suggest you give yourself a pat on the back.

Thanks

"T. Valko" wrote:

Can this not be done without an aray?

Try this:

=SUMPRODUCT(MAX((A$2:A$7=E2)*B$2:B$7))

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hi TV,

Can this not be done without an aray?

Other users need to use the document and they are not savy.

"T. Valko" wrote:

With your DB in the range A2:B7...

Unique unit codes in the range E2:E4...

Enter this array formula** in F2 and copy down to F4:

=MAX(IF(A$2:A$7=E2,B$2:B$7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Sunnyskies" wrote in message
...
Hello from Sunny RSA,

I have the following database:
Unit Kilometers
98R01 109145
06R02 134873
06R02 129523
98R01 110987
07T03 98654
07T03 101345
Now I need a formula that will go and pick the highest kilometers to
populiate the following table:
98R01
06R02
07T03

The result should be
98R01 110987
06R02 134873
07T03 101345

The unit numbers are unique, there is no between themselves.

I think MAX needs be used, but I am not sure how to apply this to a
criteria.

Thanks