Building a tool in Excel and as part of the exercise I need to identify the top 50 securities based on market cap.
This is an easy enough thing to do using RANK(), but I’ve got a universe of over 10,000 and processing of an otherwise lightening quick workbook gets sluggish once I add the rank over these 10,000 entries.
I realize the sluggishness is due at least in part to Excel needing to sort the entries in order to rank them so I’m wondering if some kind of index on the values to be ranked could speed things up? Is there a better way to do this, either via an Excel function(s) that I’m unfamiliar with or using a some other approach to speed things up?
You could sort the values the way you want and then use a function like ROW() to simply assign the rank sequentially (although you might need to pay do something special to break “ties”).
If you don’t want to sort the values, it depends on what you need…
Case 1 — You don’t actually need the rank, but only need to identify the top 50 values.
Suppose your values are in cells A1:A10000.
In another cell, say E1, enter a formula that calculates the “cutoff” for the top 50 values like this
In cell B1, enter a formula that returns the value in A1 if it is below the “cutoff”, or an empty string otherwise like this
Copy this formula down to cells B2 through B10000.
The rows with top 50 values in column A will have non-blank values in column B.
There may be more than 50 such rows if there are ties.
Case 2 — You need the rank, but only for the top 50 values.
Follow the instructions for Case 1, then, in cell C1, enter a formula that calculates the rank for the non-blank values in column B like this
Copy this formula down to cells C2 through C10000.
The rows with top 50 values in column A will be ranked in column C.
Again, there may be more than 50 such rows if there are ties. There will also be gaps in the rank values if there are ties.
Case 3 — You need the rank for all 10000 values.
Use RANK like you are now.
The fastest way to generate a list of the Top 50 is to utilize the auto-filter in Excel, which is faster than any loop you can write.
Here’s an example of how to do it assuming:
- Row 1 is a header column
- In the example, column G is the row with the Market Cap
How it works: This is a ducttape solution, but works really well.
- Copies the current sheet to a new sheet called “Top 50”
- Applies auto-filter and filters it in descending order (highest numbers first)
- Deletes all rows after the 50th entry
- Select A1 just for to make it pleasing to the eyes 🙂
There are a ton of ways to improve this code such as:
- Sloopy assumptions on where end of column is
- Add a column for rank and plug in 1 through 50
I think this code is a good place to start, though. Adjust the column G to the column with your data and give it a try!
Sub FilterbyDescending() ' filter row G in desceneding order Dim filter As Range Set filter = Range("A1:G100000") filter.Sort Key1:=Range("G2:G1000000"), _ Order1:=xlDescending, _ Header:=xlYes, _ Ordercustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub CreateBackup() Application.ScreenUpdating = False 'Create the Top 50 sheet, paste current sheet to it Dim top50sheet As Variant Application.ActiveSheet.Copy After:=Sheets(Application.Worksheets.Count) Set top50sheet = Application.ActiveSheet top50sheet.Name = "Top 50" Dim rowCount As Long Dim columCount As Long Call FilterbyDescending ' Time to delete everything after the top 50 Range("A52").CurrentRegion.Select rowCount = Selection.Rows.Count columnCount = Selection.Columns.Count Range(Cells(52, 1), Cells((rowCount), columnCount)).Delete Cells(1, 1).Select Application.ScreenUpdating = True End Sub