Three level ranking

R

Raju Kumar Singh

New Member
Joined
Jul 12, 2017
Messages
15
Hey guys..

Please help me to write a VBa code for the below scenario.

I have five columns. First one have names. 3rd, 4th, 4th have criteria. First, give ranking as per 3rd columns, if any output rank have same ranking then it will go to 4th column criteria. If after applying ranking again same ranks are there then will check with 5th column criteria. Final ranking will be kept in 2nd column.

[TABLE="width: 323"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD] Rank[/TD]
[TD]criteria 1[/TD]
[TD]Criteria 2[/TD]
[TD]Criteria 3[/TD]
[/TR]
[TR]
[TD]ARR[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NET[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]JUNb[/TD]
[TD] [/TD]
[TD]45[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PRO[/TD]
[TD] [/TD]
[TD]23[/TD]
[TD]12[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]NEC[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]JUN[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]RKSI[/TD]
[TD] [/TD]
[TD]15[/TD]
[TD]32[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]JUNk[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]JUNe[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]TS [/TD]
[TD] [/TD]
[TD]33[/TD]
[TD]11[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]ING[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]DIM[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]JUNt[/TD]
[TD] [/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AVN[/TD]
[TD] [/TD]
[TD]6[/TD]
[TD]36[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]MAR[/TD]
[TD] [/TD]
[TD]29[/TD]
[TD]15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NIS[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]JUNx[/TD]
[TD] [/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PT.[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Thanks...
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Do you need VBA? The following formula does what you want:

ABCDEF
ARR

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Rank[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]criteria 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Criteria 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Criteria 3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NET[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JUNb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PRO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]33[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NEC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JUN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]RKSI[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JUNk[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JUNe[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]TS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]33[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ING[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]DIM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JUNt[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AVN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]36[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]MAR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NIS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JUNx[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PT.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--(C$2:C$19*10000+D$2:D$19*100+E$2:E$19>C2*10000+D2*100+E2))+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



It should work as long as none of the values exceeds 100. If that's a possibility, raise the 100s in the formula to 1000, and the 10000s to 1000000.
 
Upvote 0
Sure:

Code: 
Sub rank1()
Dim lr As Long, MyData As Variant, i As Long, j As Long

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    MyData = Range("A2:E" & lr).Value
    For i = 1 To UBound(MyData)
        MyData(i, 2) = 1
        For j = 1 To UBound(MyData)
            If MyData(j, 3) < MyData(i, 3) Then GoTo NextJ:
            If MyData(j, 3) > MyData(i, 3) Then
                MyData(i, 2) = MyData(i, 2) + 1
                GoTo NextJ:
            End If
            If MyData(j, 4) < MyData(i, 4) Then GoTo NextJ:
            If MyData(j, 4) > MyData(i, 4) Then
                MyData(i, 2) = MyData(i, 2) + 1
                GoTo NextJ:
            End If
            If MyData(j, 5) > MyData(i, 5) Then MyData(i, 2) = MyData(i, 2) + 1
NextJ:
        Next j
    Next i
    Range("A2:E" & lr).Value = MyData
        
End Sub
 
Upvote 0
I've been playing around with ArrayLists, and here's a case where using one results in a shorter macro, which I'll present for interest:

Code: 
Sub rank2()
Dim i As Long, AL As Object

    Set AL = CreateObject("System.Collections.ArrayList")
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        AL.Add Format(Cells(i, 3), "00") & Format(Cells(i, 4), "00") & Format(Cells(i, 5), "00") & Cells(i, 1)
    Next i
    AL.Sort
    AL.Reverse
    For i = 0 To AL.Count - 1
        Range("A1:A19").Find(Mid(AL.Item(i), 7), , , xlWhole).Offset(, 1) = i + 1
    Next i
    
End Sub
 
Upvote 0
Raju Kumar Singh said:
Its awesome!! Is it possible to get the same output with the help of VBA code as well?
Click to expand...
In that case, couldn't we just apply Eric's formula approach through vba?
Code: 
Sub Rank0()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = Replace("=SUMPRODUCT(--(C$2:C$#*10000+D$2:D$#*100+E$2:E$#>C2*10000+D2*100+E2))+1", "#", Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Value
  End With
End Sub
 
Upvote 0
Hi Raju,

the concept behind the formula is simple, for any row, just count the number of other rows that are outrank this row, and that's the rank. If you have a row with 0 others that outrank it, it's rank is 0 + 1. If you have a row where 2 other rows outrank it, it's rank is 2 + 1, or 3.

To make comparing ranks easy, we combine all 3 criteria into a single value. For example, row 8 RKSI, the criteria are 15, 32, and 3. Multiply 15 by 10000 = 150000, multiply 32 by 100 = 3200, and add them together 150000 + 3200 + 3 = 153203. Compare that to another row, say 161602 (row 14), and it's easy to see that row 14 is bigger. If there is a tie in the first criteria, like 50603 and 50609 (rows 2 and 17) you can see row 17 wins, but it has to go to the third criteria.

Finally, now that we see the basic idea, we just have to find a way to compare all the rows at a time. The SUMPRODUCT is an array function, it lets you perform actions on a range of data.

=SUMPRODUCT(--(C$2:C$19*10000+D$2:D$19*100+E$2:E$19>C2*10000+D2*100+E2))+1

The part in red calculates the number for the entire range, the part in blue calculates the number for an individual row. This results in 18 comparisons, giving 18 TRUE/FALSE answers. The -- is called a double unary and basically converts TRUE/FALSE into 1/0. Then the SUM part of SUMPRODUCT adds up the 1s, and we add the extra 1, and we're done.

Hope this explains things for you.

Peter, your implementation of the macro is shorter, and no doubt more efficient than either of my versions. But the OP wanted a "different" solution than a formula, so I decided to try an approach that's not just a reconfiguration of the formula. Both of my versions can be improved, but I did learn something in the process so that's a plus for me anyway.
 
Upvote 0
Eric W said:
Peter, your implementation of the macro is shorter, and no doubt more efficient than either of my versions. But the OP wanted a "different" solution than a formula, so I decided to try an approach that's not just a reconfiguration of the formula. Both of my versions can be improved, but I did learn something in the process so that's a plus for me anyway.
Click to expand...
Hi Eric, I'm not sure about the "different" solution desire but trying something different is often rewarding. :)

I do note that your ArrayList macro does not produce the same results as the SUMPRODUCT formula. Test where there are 2 or more rows with identical criteria, as with rows 2 & 3 in my sample below.

Here is a similar idea but using a SortedList instead of an ArrayList.
I have assumed that if there are ties, the rows are ranked equally and then the next rank(s) are skipped until a different set of criteria is encountered, like the SUMPRODUCT formula did.

Code: 
Sub rank2a()
  Dim i As Long
  Dim SL As Object
  Dim s As String

  Set SL = CreateObject("System.Collections.Sortedlist")
  For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    s = Format(Cells(i, 3), "00") & Format(Cells(i, 4), "00") & Format(Cells(i, 5), "00")
    If SL.ContainsKey(s & "-9999") Then
      SL.Add s & Format(i, "-0000"), 1
    Else
      SL.Add s & "-9999", 1
    End If
  Next i
  For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(i, 2).Value = SL.Count - SL.IndexOfKey(Format(Cells(i, 3), "00") & Format(Cells(i, 4), "00") & Format(Cells(i, 5), "00") & "-9999")
  Next i
End Sub

My sample data and code results:


Book1
ABCDE
1NameRankcriteria 1Criteria 2Criteria 3
2ARR11563
3NET11563
4JUNb145251
5PRO4231233
6NEC81228
7JUN16023
8RKSI715323
9JUNk1502512
10JUNe1431220
11TS2331110
12ING170023
13DIM1341513
14JUNt616162
15AVN963619
16MAR329153
17NIS10569
18JUNx522110
19PT.180015
Rank (2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
261
Fluff
Fluff
V
  • Question Question
Rank function
2
Replies
12
Views
360
Fluff
Fluff
V
  • Solved
Rank Function
Replies
4
Views
288
vmjan02
V
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
122
ellison
E
S
  • Solved
Individual Ranking
2
Replies
11
Views
433
sksanjeev786
S

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

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