highest # in a column A & returns answer from column B Formula

[Guest]

I need help creating a formula in excel. I have two columns of info i would
like to analyze column a for the highest total once identified take the name
from column b and put the result in column c. I need to do this for 22
players and show the results from highest to lowest.

Column A Column B Column C-result

Goals Player leaders

5 a H
15 b c
22 c f
3 d e
18 e b
19 f a
0 g d
40 h g

 

[Guest]

Sort cols A & B by A descending. Then copy the result in col B into col C.

 

[Sandy Mann]

One way:

Insert a new Column A and fill with numbers in assending order. Assuming
that the data in now in Columns B & C, highlight the data in Columns A, B &
C and sort on Goals. Copy the sorted Player data and paste into the Leaders
column. Now highlight A,B & C and sort on Column A to put the data back
into the original order. Finally delete the helper column A

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

=INDEX($B$2:$B$9,MATCH(LARGE($A$2:$A$9,ROW(1:1)),$A$2:$A$9,0))


"Randy" skrev:

 

[Guest]

This was excellent, i have one more question if two playrers have the same
total the formula grabs the first name. Sometimes two players are tied and i
need to have both in order, can this formula be tweaked to accomodate this.
Example:

Player a has 5 goals, player b has 9 goals player c has 5 goals the order
should show

Player b-9
player a-5
player c-5

any suggestions?

I almost have this working perfect and your help is appreciatted.

 

[MartinW]

Hi Randy,

Try it like this,

Put this in C2 and drag down as far as you need.
=A2-ROW()/10^10

Then put Excellent's formula in column D (adjusted to reference C instead of
A)
i.e.
=INDEX($B$2:$B$9,MATCH(LARGE($C$2:$C$9,ROW(1:1)),$C$2:$C$9,0))

Hide column C if you wish.

HTH
Martin

 

[Guest]

TOTALLY AND COMPLETELY AWESOME< YOU THE MAN!!!!!!!!!!!!!!!!!!!!!!!!!