Largest number in column

[peteewheat11]

I am working with football stats.

Column A is the player number. Column B is the yards for each play. A
typical sheet could look like:

<Player> <Yards>
10 21
10 14
10 7
14 1
14 3
10 22
14 11

I have a separate sheet that has each player number in a cell. I need to
find and display the longest yard made by each player.

10 (Longest Yards)
14 (Longest Yards)

Any help would be greatly appreciated!

 

[Jacob Skaria]

In Sheet2 try the below formula..and copy down as required

Col A Col B
Player Max Yards
10 =MAX(IF((Sheet1!A1:A10=A2),Sheet1!B1:B10))
14 =

If this post helps click Yes

 

[Jacob Skaria]

Forgot to mention that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes

 

[peteewheat11]

Super! Thanks for the very quick response!

Forgot to mention that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes

 

[Shane Devenshire]

Here is a non-array solution:

=SUMPRODUCT(MAX((Sheet1!A2:A12=A1)*Sheet1!B2:B12))