Conditional Rank in Excel

[Niraj Baraili]

Hi Experts,

I have id no in col A, time interval in col b. Based on time interval, i have to give rank for which i have used formula as below:

=SUMPRODUCT(--(A:A=A2)*(B2>B:B))+1

Since i have huge amount of data which makes the file very slow. Is there any alternative or VBA code which perform the above task without slowing down the system.

Also, just wondering can we use the Scripting.Dictionary concept ? if yes, how ?

 

[Somendra Misra]

Hi Niraj,

See the attached file for pivot table solution, try in your original workbook.

Regards,

 

[Niraj Baraili]

Hi Somendra,

Thank you for the solution.

But since i have a workbook with more than 20 fields, i have do this pivot activity in different sheet and display the output along with the 20 fields.

Can we try different concept ?

 

[Somendra Misra]

May be a sample file with raw data with all the fileds, will help to understand how exactly it will work.

Regards,

 

[Niraj Baraili]

Hi SM,

I don't have the file as i have asked this question somewhere in a interview.

 

[Chihiro]

Sumproduct is bit annoying to deal with in vba (due to difference in how TRUE/FALSE behaves in VBA vs. Worksheet). Try reading through link to see if you can use one of the approach explained by Andrew Paulsom.
http://www.mrexcel.com/forum/excel-...-function-visual-basic-applications-code.html