How to grade numbers as 1st, 2nd etc based on their values

Question

0.00/5 (No votes)

See more:

hello everyone. 
I am trying to rate a group of numbers in a datagrid containing two columns. I want the cells of the Coulmn B to have values as 1st, 2nd, 3rd etc, based on the values of Column A.

For example:

Col A            Col B
80                    2
305                   1
23                    5
43                    4
31                    3

What I have tried:

All I know is how to assign the result to the cells of Column B after its found

Dim iRow As Integer
        Dim cellA As Integer
        Dim cellB As Integer

        For iRow = 0 To Me.DataGridView1.RowCount - 1

            Me.DataGridView1.Rows(iRow).Cells(1).Value = cellA
        Next
        iRow = irow + 1

Posted 11-Jun-18 5:14am

SamuelDexter

Updated 9-Jun-23 12:42pm

Add a Solution

Comments

Richard MacCutchan 11-Jun-18 11:19am

If you sort the grid on column A in descending order then you can set the rankings in column B with s simple counter.

SamuelDexter 11-Jun-18 11:46am

Off course thats, the simplest way to go. I agree but I for some reasons decided to disabled column header sorting after all because using the common

Datagridview1.Sort(Datagridview1.Columns(1), System.ComponentModel.ListSortDirection.Ascending)

has never worked for me; always errors :(

Richard MacCutchan 11-Jun-18 12:01pm

"always errors :("
Then please edit your question, show the code and explain exactly what errors.

SamuelDexter 11-Jun-18 13:08pm

System.ArgumentException: 'Object must be of type String.'

Richard MacCutchan 11-Jun-18 13:38pm

So what part of that do you not understand?

SamuelDexter 11-Jun-18 13:53pm

I realized that it seems the column form which you are sorting should be of the same data type as the very first column. So for example if you did a sheet numbering
on the first like

' Numbering

For r = 0 To DataGridView1.RowCount - 1
DataGridView1.Rows(r).Cells(0).Value = r
Next
r = r + 1

since its integer then the next column should have its values as integer appropriately, else you will need to do some conversions here and there where necessary.
So in this case I had to ensure that the second col populated with an integer counter and the main column A entries were integers as well.

I guess I figured it out. Thanks man. Im grateful for your time and assistance. Cheers :)

Richard MacCutchan 12-Jun-18 3:30am

That does not make sense, individual columns may be any data type. The Sort method will operate on any column as long as its contents are sortable; see DataGridView.Sort Method (DataGridViewColumn, ListSortDirection) (System.Windows.Forms)[^].

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-06-12T05:52:00

Solution 1

Something like this should work, and will correctly rank ties:

VB.NET

Dim cumulativeRank As Integer = 1
For Each group As IGrouping(Of Object, DataGridViewRow) In DataGridView1.Rows.Cast(Of DataGridViewRow)().GroupBy(Function (r) r.Cells(0).Value).OrderByDescending(Function (g) g.Key)
    Dim rank As Integer = cumulativeRank
    For Each row As DataGridViewRow In group
        row.Cells(1).Value = rank
        cumulativeRank += 1
    Next
Next

If you don't care about ties, then you don't need the grouping:

VB.NET

Dim rank As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows.Cast(Of DataGridViewRow)().OrderByDescending(Function (r) r.Cells(0).Value)
    row.Cells(1).Value = rank
    rank += 1
Next

Posted 12-Jun-18 5:52am

Richard Deeming

Comments

Maciej Los 12-Jun-18 17:13pm

On the first look: when datagridview stores unique values, your code is OK, but when there are duplicates your "ranking" method will return wrong result. ;(
By the way - i was able to understand what OP want to achieve only after reading your answer.
A 4!

Richard Deeming 13-Jun-18 5:55am

That's why there are two versions.

* Use the second version if you don't care about ties - similar to SQL's ROW_NUMBER function.
* Use the first version if you do care about ties, and want them ranked properly - similar to SQL's RANK function.

If you want something similar to DENSE_RANK, where ties don't leave gaps, then it would be trivial to adapt the first version:

Dim rank As Integer = 1
For Each group As IGrouping(Of Object, DataGridViewRow) In DataGridView1.Rows.Cast(Of DataGridViewRow)().GroupBy(Function (r) r.Cells(0).Value).OrderByDescending(Function (g) g.Key)
    For Each row As DataGridViewRow In group
        row.Cells(1).Value = rank
    Next
    
    rank += 1
Next

Maciej Los 13-Jun-18 6:02am

Oh... i see. It was late yesterday...
Upvoted!

Maciej Los · Answer 2 · 2018-06-12T11:09:00

In addition to Richard Deeming[^]'s solution and my comment to His solution...

I'd suggest to use Dictionary[^] object when there are rows with duplicate values in ColA

VB.NET

Dim dgv As DataGridView = Me.DataGridView1
Dim oDict As Dictionary(Of Integer, Integer) = dgv.Rows().Cast(Of DataGridViewRow) _
	.OrderByDescending(Function(r) r.Cells("ColA").Value) _
	.GroupBy(Function(r) r.Cells("ColA").Value) _
	.Select(Function(grp, index) New KeyValuePair(Of Integer, Integer)(grp.Key, index+1)) _
	.ToDictionary(Function(kvp) kvp.Key, Function(kvp) kvp.Value)
	
For Each dgr As DataGridViewRow In dgv.Rows
	dgr.Cells(1).Value = oDict(dgr.Cells(0).Value)
Next

Sample output:

As you can see, 305 gets 1. rank, 80 - 2. and so on...

Good luck!

[EDIT]
As to the Richard's comment to my answer - small improvement (still using Dictionary object):

VB.NET

Dim oDict As Dictionary(Of Integer, Tuple(Of Integer, Integer)) = dgv.Rows().Cast(Of DataGridViewRow) _
	.GroupBy(Function(r) r.Cells("ColA").Value) _
	.OrderByDescending(Function(g) g.Key) _
	.Select(Function(grp, index) New KeyValuePair(Of Integer, Tuple(Of Integer, Integer))(grp.Key, Tuple.Create(index+1, grp.Count-1))) _
	.ToDictionary(Function(kvp) kvp.Key, Function(kvp) kvp.Value)

For Each dgr As DataGridViewRow In dgv.Rows
	Dim cumulative As Integer = oDict.TakeWhile(Function(x) x.Key>dgr.Cells(0).Value).Sum(Function(x) x.Value.Item2)
	dgr.Cells(1).Value = oDict(dgr.Cells(0).Value).Item1  + cumulative
Next

Result:

Member 15876310 · Answer 3 · 2023-06-09T12:42:00

    Dim oDict As Dictionary(Of Integer, Tuple(Of Integer, Integer)) = DataGridView1.Rows().Cast(Of DataGridViewRow).GroupBy(Function(r) r.Cells(10).Value).OrderByDescending(Function(g) g.Key) _
.Select(Function(grp, index) New KeyValuePair(Of Integer, Tuple(Of Integer, Integer))(grp.Key, Tuple.Create(index + 1, grp.Count - 1))) _
.ToDictionary(Function(kvp) kvp.Key, Function(kvp) kvp.Value)

    For Each dgr As DataGridViewRow In DataGridView1.Rows
        Dim cumulative As Integer = oDict.TakeWhile(Function(x) x.Key > dgr.Cells(10).Value).Sum(Function(x) x.Value.Item2)
        dgr.Cells(13).Value = oDict(dgr.Cells(10).Value).Item1 + cumulative
    Next

81.
Marks	Grade     Remark            Position
68.2	4	      High Average	    4
63.2	4	      High Average	    5
56.2	5	      Average	        6
39.5	8	      Lower	            10
57.5	5	      Average	        7
68.2	4	      High Average	    4
68.5	4	      High Average	    1
50.3	6	      Low Average	    8
68.5	4	      High Average	    1
43.7	7	      Low	            9
68.3	4	      High Average	    3
35.5	8	      Lower	            11

i had an error, it keeps on telling me "the object must be double"

<pre>81.
the results should be

Marks	Grade     Remark            Position
68.2	4	      High Average	    4
63.2	4	      High Average	    5
56.2	5	      Average	        6
39.5	8	      Lower	            10
57.5	5	      Average	        7
68.2	4	      High Average	    4
68.5	4	      High Average	    1
50.3	6	      Low Average	    8
68.5	4	      High Average	    1
43.7	7	      Low	            9
68.3	4	      High Average	    3
35.5	8	      Lower	            11

How to grade numbers as 1st, 2nd etc based on their values

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0