Ranking with Multiple Columns

C

CoachJava

New Member
Joined
Nov 25, 2013
Messages
9
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!

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Student[/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ANDREW[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]BRAD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]CURTIS[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]DANIEL[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]ERIC[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]20[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Maybe something like this


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

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Multiple conditions
Replies
8
Views
641
awoohaw
awoohaw
T
  • Solved
Transposing(?) data from row into multiple shorter columns
Replies
5
Views
289
arun_eagertolearn
A
A
  • Question Question
How to rank multiple datasets by suppliers across 6 columns
Replies
2
Views
590
Aerobus
A
V
  • Solved
Sports competition top scorer list (round by round)
2
Replies
17
Views
232
Kaper
Kaper
VinceF
  • Question Question
Formula To Show Result
Replies
1
Views
581
VinceF
VinceF

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top