Gradebook Ranking Help

uscget3

New Member
Joined
Nov 30, 2005
Messages
1
Hello. I am trying to develop a means to sort my class scores in ascending order. The trouble I'm having is trying to get the names of the students and their grades to sort with their respective rank. The following is an example of the sheet I have currently:

Rank Name GPA
6 Will 0.2667
4 Ray 0.3333
1 Smith 0.4800
2 Stan 0.4133
2 Bill 0.4133
4 Jones 0.3333

I used the RANK function and it works to rank the GPA only. I'm stuck on how to include the names and have the order sorted automatically. Any help would be greatly appreciated. Thanks.

Gabe
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi, Welcome to the Board!

How about a worksheet change event:
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Integer
Dim lRow As Long

'*******************************
'** Exit if changing > 1 cell **
'*******************************
If Target.Cells.Count <> 1 Then Exit Sub

'**********************************************************
'** Exit if not changing column A,B or C, rows 2 onwards **
'**********************************************************
If Target.Column > 3 Then Exit Sub
lRow = Target.Row
If lRow = 1 Then Exit Sub

'***********************************************************
'** Exit if any cell in current row, columns B:C is blank **
'***********************************************************
For iCol = 2 To 3
    If Cells(lRow, iCol).Text = "" Then Exit Sub
Next iCol

Application.EnableEvents = False
Cells(lRow, "A").Formula = "=Rank(C" & lRow & ",C:C)"

Columns("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending, _
                    Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes
Application.EnableEvents = True
End Sub

To install, right-click the sheet tab, select 'View Code' and paste above into code window.

To use, just type anything into columns B & C. - it automatically enters the ranking in column A.
 

jdee

Active Member
Joined
Sep 10, 2003
Messages
276
Perhaps I'm missing something here - but I just tried this using your list and using Data - Sort, and what I wanted to sort by and all worked fine. All names etc. stay with each other.
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi JD,

yes, you're right, but the sheet change event sorts the data automatically when you've entered data into the cells in cols B & C.
 

jdee

Active Member
Joined
Sep 10, 2003
Messages
276

ADVERTISEMENT

Alan:

Just tried your code and, of course, it works just as you say. I usually only record macro's and would not know how to write code such as you have. I envy your ability and usually try to apply what I see on the forum to gain understanding and to keep knowledge base going since retiring.

I have read your other answers and find all interesting. I, along with many others I'm sure, thank you.
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
jdee said:
Alan:

Just tried your code and, of course, it works just as you say. I usually only record macro's and would not know how to write code such as you have. I envy your ability and usually try to apply what I see on the forum to gain understanding and to keep knowledge base going since retiring.

I have read your other answers and find all interesting. I, along with many others I'm sure, thank you.

Why thank you JD, you'vemade me all embarrassed now :oops:
 

blergh

New Member
Joined
Dec 3, 2012
Messages
1
Hello. I am trying to develop a means to sort my class scores in ascending order. The trouble I'm having is trying to get the names of the students and their grades to sort with their respective rank. The following is an example of the sheet I have currently:

Rank Name GPA
6 Will 0.2667
4 Ray 0.3333
1 Smith 0.4800
2 Stan 0.4133
2 Bill 0.4133
4 Jones 0.3333

I used the RANK function and it works to rank the GPA only. I'm stuck on how to include the names and have the order sorted automatically. Any help would be greatly appreciated. Thanks.

Gabe[Hi]

Well, you're using the RANK function wrong. Say in the GPA column starting(Will's grade) is A1, type in this function
RANK(A1;A$1:A6)
Alternatively, you can also make the rank into Roman Numerals with
ROMAN(RANK(A1;A$1:A6))
Hope this helps
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,621
Messages
5,832,736
Members
430,160
Latest member
a_majda

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
Top