Ranking with VBA - no gaps between ranks

xogregor

New Member
Joined
Oct 12, 2013
Messages
7
I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.

Ext P$ Rank Should be
2,128.34 1 1
2,128.34 1 1
1,023.78 3 2
822.72 4 3

<tbody>
</tbody>

I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times

Code:
=SUM(1/COUNTIF(A$2:A$35000;A$2:A$35000)*(A$2:A$35000>A2))+1

I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly. Maybe someone here can help, it would be greatly appreciated


Thanks
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Here's an example for values in column A with a header in A1 that includes a sort.
Code:
Sub RankNoGaps()
Dim lR As Long, c As Range, ct As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = false
Range("A2").Sort key1:=[A2], order1:=xlDescending, Header:=xlYes
Range("B1").Value = "Rank"
ct = 1
For Each c In Range("A2:A" & lR)
    If c.Row = 2 Then
        c.Offset(0, 1).Value = 1
    ElseIf c.Value = c.Offset(-1, 0).Value Then
        c.Offset(0, 1).Value = c.Offset(-1, 1).Value
    Else
        ct = ct + 1
        c.Offset(0, 1).Value = ct
    End If
Next c
Application.ScreenUpdating = True
End Sub
 

xogregor

New Member
Joined
Oct 12, 2013
Messages
7
Amazing, thanks! One follow-up question though: How would I change the code to work on multiple columns, I havent used offset often, so not a 100% sure how I would do it exactly. Simplified example of data setup below

Ext P$
Ext P$Ext P$Rank1Rank2Rank3
2,128.341,583.892,004.63
2,128.341,898.951,998.97
1,023.782,054.17865.27
822.721,645.451,837.42
782.64782.64347.84

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
 

xogregor

New Member
Joined
Oct 12, 2013
Messages
7
Actually, nevermind, I figured it out myself!
Thanks a lot for the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

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
Top