How to rank 2 columns based on their values

Question

0.00/5 (No votes)

See more:

Hello everyone, hoping to answer my question.

I am trying to rank group of scores in a datagridview containing 4 columns. I want the cells of the column B to have values as 1, 2, 3, based on the values of Column A.

And for the Column D, I want to rank it based on Column B and C.

For example:

Col A            Col B            Col C           Col D
Score            Rank           TieBreaker      Final Rank

6.0               3               3.53             3
7.0               2               3.40             2
6.0               3               3.50             4
9.5               1               5.90             1

What I have tried:

VB.NET

Dim dgv As DataGridView = Me.DataGridView
        Dim oDict As Dictionary(Of Integer, Integer) = dgv.Rows().Cast(Of DataGridViewRow) _
    .OrderByDescending(Function(r) r.Cells(0).Value) _
    .GroupBy(Function(r) r.Cells(0).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(4).Value = oDict(dgr.Cells(0).Value)
        Next

this is for Column B. I only need for the column D. Thank you in advance!

Posted 31-Jul-23 9:48am

JOEY VERANO JR.

Updated 31-Jul-23 10:49am

Maciej Los

v5

Add a Solution

1 solution

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)

Maciej Los · Answer 1 · 2023-07-31T10:49:00

Solution 1

Well... I'd suggest to work on data instead of datagridview cells.

Here is an idea:

Sub Main()
	Dim numbers AS Integer() = New Integer(){8, 9, 1, 10, 11, 2, 12, 13, 3, 3, 5, 14, 15, 16, 17, 18, 8, 8, 8, 8, 19, 19}
	Dim result  As List(Of Tuple(Of Integer, Integer)) = numbers. _
		OrderByDescending(Function(x) x). _
		GroupBy(Function(x) x). _
		SelectMany(Function(grp, i) grp. _
			Select(Function(g)  New Tuple(Of Integer, Integer)(i+1 , g))). _
		ToList()
	
	'you can use result to display value + rank
End Sub

Result:

Rank  Value
1     19 
1     19 
2     18 
3     17 
4     16 
5     15 
6     14 
7     13 
8     12 
9     11 
10    10 
11     9 
12     8 
12     8 
12     8 
12     8 
12     8 
13     5 
14     3 
14     3 
15     2 
16     1

Posted 31-Jul-23 10:49am

Maciej Los

Comments

JOEY VERANO JR. 1-Aug-23 4:57am

Thank you! please help me for the Column D (Final Rank), I want to rank it based on Column B(Rank) the ties score will base on Column C (TieBreaker)

Maciej Los 1-Aug-23 12:18pm

And what is the logic behind it?

JOEY VERANO JR. 2-Aug-23 4:16am

the logic is if the colB(RANK) does have a same score, the colD(Final Rank) will base on the tiebreaker score to rank it.

Based on my example in Col-A there are 2 ties which is 6.0. In Col-B it ranked with same place which is 3rd. The Col C(Tiebreaker) is also given together with Col-A.

In Col-D this will base on Col-B if there are same score and ranked it base on Col-C.

JOEY VERANO JR. 2-Aug-23 7:21am

This is the last problem for my system. Hoping you understand my question. Thank you!

Maciej Los 2-Aug-23 17:24pm

This portion of data is too small to provide an algorithm. I'd suggest to divide the value of column C by 1. rank (column B).
Result:

Value  Rank (Col D)
1.18   3
1.70   2
1.17   4
5.90   1

Is that what you want?

JOEY VERANO JR. 3-Aug-23 1:20am

I mean how to rank by multiple criteria in datagridview.

JOEY VERANO JR. 3-Aug-23 1:48am

To clarify, I'm trying to make a scoring system for a competition and I faced a problem when it comes to ranking the competitors.

An example can be found here: https://docs.google.com/spreadsheets/d/1lbnmHfW2Y2g8JR8PRZSaIRGgLM3n4rFvqZ2WOVjCQxU/edit#gid=0

The competitors should be ranked by these rules:

Final Score, if this is a tie, then by
TieBreaker, if this is also a tie, then they share a rank in final rank

I've been able to get to the 1st rule (where it rank the final score) but beyond that I'm out of knowledge.

Thank you!