From range and criteria select highest value

[Sunnyskies]

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

 

[Max]

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

 

[T. Valko]

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)

 

[Sunnyskies]

Hi TV,

Can this not be done without an aray?

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

 

[Max]

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

---

 

[Sunnyskies]

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

 

[T. Valko]

Can this not be done without an aray?

Try this:

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

 

[Sunnyskies]

Hi TV,

Excellent.

Suggest you give yourself a pat on the back.

Thanks

 

[T. Valko]

You're welcome. Thanks for the feedback!