Ranking with Multiple Columns

[CoachJava]

I have a project that I am unable to figure out the best way how to accomplish. I have five players on our basketball that are having a contest. Each player took 25 shots. I need to rank each player's shots individually. For instance, Andrew made 24 and 23 (4th and 1st attempts respectively) so he should be 1st and 2nd while Curtis would get 3rd because he was next with 22 made shots on his last attempt.

I would like to rank the numbers multiple columns (columns B thru F) in column G (not shown but understood).

If this is not possible, is there a way to list ALL the numbers in column G using a formula (the number of students will continue to grow so I am unable to simply enter =B2 in cell G2 and restart with =C2 in cell G7 (after five entries) because the number of students will grow.

Any answer to either would be much appreciated. Cheers!

	A	B	C	D	E	F
1	Student	1st	2nd	3rd	4th	5th
2	ANDREW	23	18	19	24	15
3	BRAD	4	3	8	10	2
4	CURTIS	16	13	14	17	22
5	DANIEL	7	6	5	1	0
6	ERIC	9	21	11	12	20

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Student​	1st​	2nd​	3rd​	4th​	5th​	Ranking​	Score​	Student​
2​	ANDREW​	23​	18​	19​	24​	15​	1​	24​	ANDREW​
3​	BRAD​	4​	3​	8​	10​	2​	2​	23​	ANDREW​
4​	CURTIS​	16​	13​	14​	17​	22​	3​	22​	CURTIS​
5​	DANIEL​	7​	6​	5​	1​	0​	4​	21​	ERIC​
6​	ERIC​	9​	21​	11​	12​	20​	5​	20​	ERIC​
7​							6​	19​	ANDREW​
8​							7​	18​	ANDREW​
9​							8​	17​	CURTIS​
10​							9​	16​	CURTIS​
11​							10​	15​	ANDREW​
12​							11​	14​	CURTIS​
13​							12​	13​	CURTIS​
14​							13​	12​	ERIC​
15​							14​	11​	ERIC​
16​							15​	10​	BRAD​
17​							16​	9​	ERIC​
18​							17​	8​	BRAD​
19​							18​	7​	DANIEL​
20​							19​	6​	DANIEL​
21​							20​	5​	DANIEL​
22​							21​	4​	BRAD​
23​							22​	3​	BRAD​
24​							23​	2​	BRAD​
25​							24​	1​	DANIEL​
26​							25​	0​	DANIEL​

In column G beginning in G2 create a series 1, 2, ...

Formula in H2 copied down
=IF(G2<>"",LARGE($B$2:$F$6,G2),"")

Array formula in I2 copied down
=IF(H2<>"",INDEX($A:$A,SMALL(IF($B$2:$F$6=H2,ROW($B$2:$F$6)),COUNTIF(H$2:H2,H2))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[CoachJava]

Marcelo, this is perfect. It is exactly what I needed. Thanks.

 

[Marcelo Branco]

Marcelo, this is perfect. It is exactly what I needed. Thanks.

You are welcome and thanks for the feedback.

M.