VBA Rank

[Genetu]

Asked Question for Mrxel.xlsx
	A	B	C	D	E	F	G	H
1	S.No.	Name	Sex	ID No.	(V.Good) Score	(Excellent) Score	Total	Rank
2	1	Zilan	F	HAC/157/12	14	0	14	1
3	2	Mathew	F	HAC/121/12	13	1	14	1
4	3	Mesued	F	HAC/102/12	9	2	11	3
5	4	Jason	F	HAC/101/12	5	5	10	4
6	5	Johnson	M	HAC/134/12	7	3	10	4
7	6	Azmera	F	HAC/122/12	8	1	9	6
8	7	OUSMAN	M	HAC/132/12	7	0	7	7
9	8	Ali	M	HAC/133/12	1	6	7	7
10	9	Amen	F	HAC/135/12	6	0	6	9
11	10	Vismos	F	HAC/136/12	2	3	5	10
Sheet1

Good morning members. I have counted the results of each candidate (Excellent & Very good). When I rank them using VBA (it might be the same with function), the same score with different levels has the same rank. That was good but their level (Excellent & Very good) has made the difference. Look at No. 1&2. Their total is the same but the 1st one has not scored "Excellent" but ranked "First". If I rank based on column "F" the 8th (Ali) will be the first, not correct. Is there any method to consider the value of "Excellent" to rank at first?

I have tried this code:

 Application.CutCopyMode = False
Dim List As Long
     List = Cells(Rows.Count, 7).End(xlUp).row + 1
    r = 2
    
    Do Until r = List
        Cells(r, 8).Value = Application.WorksheetFunction.Rank(Cells(r, 7).Value, Range("G2:G" & List), 0) 
    r = r + 1
    Loop
ActiveWorkbook.Sheets("Report").Columns("B:H").Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlYes

 

[offthelip]

This is not primarily an excel question since you need to decide what weighting you give to "excellent" compared to "very good" i.e is "excellent" worth twice the value of "Very Good" or 10 times the Value of "Very good" once you have made that decision, VBa can be programmed to do it. SEE Below!! which assumes the value 10

 

[bebo021999]

Option Explicit
Sub rank()
Dim lr&, i&, rng, rng2
With Worksheets("Report")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    rng = .Range("E2:G" & lr).Value
    rng2 = .Range("H2:H" & lr).Value
    For i = 1 To lr - 1
        rng2(i, 1) = rng(i, 3) + rng(i, 2) / 10
    Next
    .Range("H2:H" & lr).Value = rng2
    .Range("B2:H" & lr).Sort Key1:=.Range("H2"), Order1:=xlDescending
    .Range("H2:H" & lr).Value = .Range("A2:A" & lr).Value
End With
End Sub

 

[Genetu]

This is not primarily an excel question since you need to decide what weighting you give to "excellent" compared to "very good" i.e is "excellent" worth twice the value of "Very Good" or 10 times the Value of "Very good" once you have made that decision, VBa can be programmed to do it. SEE Below!! which assumes the value 10

First, asking for an apology. I forgot such an idea and thanks "Offthelip". I will check it and get back after a while.