Ranking Top 3 rows...

[Ram Chandran]

Hi Experts,

I have attached a excel sheet.
I need excel formula or VBA code to assign a Rank function for the top 3 rows among the n number of unique values
and assign the rank count for the top 3 values.
Actually if i go for Countif(A5:A154,A5) it takes plenty of time to execute the formula since i have 9 lakhs rows of data.
Kindly help on this.
Thanks in Advance.

 

[BobBridges]

A lakh is 10 000, right, Ram? You won't find many Americans who know that, though some Brits probably do.

I don't understand what you want, here. You want something to look at the top three rows of your worksheet and display which has the largest value in col X, and which the second hightest, and which the third highest? Probably not, because you're talking about looking at all the rows.

 

[Ram Chandran]

Hi BobBridges,

Thanks for your reply,
Actually lakh which i mean here is 100,000 rows.
Among 900,000 rows i need first three dates which is newest to older sorting format, for each numbers in the number column.
Thanks for yo

 

[AlanSidman]

With Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Numbers"}, {{"Count", each _, type table [Numbers=nullable text, Values=number, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"I",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Numbers", "Values", "Date", "I"}, {"Numbers", "Values", "Date", "I"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [I] < 4)
in
    #"Filtered Rows"

 

[BobBridges]

Is "power query" the same as PowerShell? I don't know anything about that, but I'm still trying to figure out what Ram wants.

He can sort the rows and look at the top three, but I can understand how that might be pretty slow with 900 000 rows. A faster way might be to write a VBA program that loads the relevant column into an array and then find the three largest/smallest/most-desirable/whatever values, and display the row numbers they were found in. Arrays are fast in VBA.

Hm, it occurs to me belatedly that it may be even faster to copy the relevant column to another worksheet; column A is a row #, col B is the column copied from the original sheet. Sort just those two columns—Excel sort is pretty fast—and then look at the three values you want, with the rows they came from in col A.

 

[AlanSidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com