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

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

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

Sort cols A & B by A descending. Then copy the result in col B into col C.
--
Gary''s Student - gsnu200752


"Randy" wrote:

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

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

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


Replace @mailinator.com with @tiscali.co.uk


"Randy" wrote in message
...
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

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

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


"Randy" skrev:

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

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

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.


"excelent" wrote:

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


"Randy" skrev:

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

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

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


"Randy" wrote in message
...
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.


"excelent" wrote:

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


"Randy" skrev:

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

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

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

"MartinW" wrote:

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


"Randy" wrote in message
...
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.


"excelent" wrote:

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


"Randy" skrev:

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