Gradebook Ranking Help

[uscget3]

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

 

[al_b_cnu]

Hi, Welcome to the Board!

How about a worksheet change event:

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]

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]

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]

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]

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

 

[blergh]

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