Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
Highest value in column b returning column a | Excel Worksheet Functions | |||
Formula to sum every 4th cell returns #DIV/0! error in some column | Excel Worksheet Functions | |||
Help with formula that unexpectedly returns an answer | Excel Worksheet Functions | |||
How to solve a formula so that it returns actual answer if negati. | Excel Discussion (Misc queries) |