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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
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,494
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,494
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,076
Members
412,763
Latest member
sienweiw
Top