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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,148
Messages
6,106,241
Members
448,009
Latest member
Brgrant820

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