Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Ranking data records using VBA

Posted on 2014-02-10
Medium Priority
649 Views
Dear Experts:

I got two columns on the active worksheet

Column A: Overall Count of Items
Column B: The respective rank where duplicate values get the same rank and with no skipping of subsequent ranks.

There is a wonderful formula in Cell B2 that achieves this feat. Courtesy by barryhoudini from EE.
=IF(A2="";"";SUMPRODUCT((A\$2:A\$16>A2)/COUNTIF(A\$2:A\$16;A\$2:A\$16&""))+1)
This  formula is then simply copied down.

Now comes my problem:

This formula is to be filled into B2 of a worksheet called 'Results' and then copied down using VBA. The range can vary, there could be just 16 data records, but there also could be hundreds of data records that should be ranked.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

I have attached a sample file for your convenience.

Regards, Andreas

Count      Rank
800           1
408           2
380           3
260           4
260           4
106           5
98           6
98           6
78           7
54           8
54           8
48           9
44         10
28         11
24         12

Ranking-data-records-using-VBA.xlsx
0
Question by:AndreasHermle
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 53

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39847734
Hi,

pls try

``````Sub Mactro5()
LastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
Range("B2:B" & LastRow).Formula = _
"=IF(A2=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",SUMPRODUCT((A\$2:A\$" & LastRow & ">A2)/COUNTIF(A\$2:A\$" & LastRow & ",A\$2:A\$" & LastRow & "&" & Chr(34) & Chr(34) & "))+1)"
End Sub
``````
Regards
0

Author Closing Comment

ID: 39847752
Hi Rgonzo,

I am indeed deeply impressed by your expertise.

Works like a charm. Thank you very much for your professional and swift support. I really appreciate it.

Regards, Andreas
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll