Simple Rank Macro

alocke

Board Regular
Joined
Nov 10, 2011
Messages
64
Hi,
Just a simple thing - If I want to Rank data in cells A1,A3,A5,A7 amongst one another and also cells A2,A4,A6,A8 too.

The code I'm doing is more complex than this, but if someone can tell me how to do this then I'll be sorted. I think.

Cheers,
Alex
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
To rank A1, A3, A5, and A7, and place their ranking in their corresponding cells in Column B, try...

Code:
[font=Verdana]    Range("B1, B3, B5, B7").Formula = "=RANK(A1,($A$1,$A$3,$A$5,$A$7))"
    [/font]
 
Upvote 0
To rank A1, A3, A5, and A7, and place their ranking in their corresponding cells in Column B, try...

Code:
[FONT=Verdana]    Range("B1, B3, B5, B7").Formula = "=RANK(A1,($A$1,$A$3,$A$5,$A$7))"
    [/FONT]
I mean this works perfectly, but if I was to do it for 300 odd cells is there a way I could do it so that I wouldn't have to input it as each individual cell.
 
Upvote 0
do you mean RANK all even rows with one ranking and all odd cells with another ranking ???
 
Upvote 0
Hi guys by 'odd' I meant 300 cells in total. So by that I mean ranking cells A1,A3,A5...... A299 between one another and then A2,A4,A6....A300 between one another.

So instead of writing it as a range in which one has to write every single cell involved can you define the range?
 
Upvote 0
See if this macro does what you want. Note that the results are not dynamic. That is, if the original data is changed, the macro would need to be run again (though this could be automated too).

I suggest that you test on a small dummy worksheet with much fewer rows so you can more easily check the results.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RankAlternateRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> DataCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A"   <SPAN style="color:#007F00">'<- Column data is in</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2            <SPAN style="color:#007F00">'<- First data row</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns(DataCol).Offset(, 1).Resize(, 3).Insert<br>    lr = Cells(Rows.Count, DataCol).End(xlUp).Row<br>    rws = lr - fr + 1<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Cells(fr, DataCol).Resize(rws)<br>        a = .Value<br>        b = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rws - 1 <SPAN style="color:#00007F">Step</SPAN> 2<br>            a(i, 1) = vbNullString<br>            b(i + 1, 1) = vbNullString<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">If</SPAN> rws Mod 2 = 1 <SPAN style="color:#00007F">Then</SPAN> a(rws, 1) = vbNullString<br>        <br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 3)<br>            .Value = a<br>            <SPAN style="color:#00007F">With</SPAN> .Offset(, -1)<br>                .FormulaR1C1 = _<br>                    "=RANK(RC[1],R" & fr & "C[1]:R" & lr & "C[1])"<br>                .Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Value = b<br>            <SPAN style="color:#00007F">With</SPAN> .Offset(, -2)<br>                .FormulaR1C1 = _<br>                    "=RANK(RC[2],R" & fr & "C[2]:R" & lr & "C[2])"<br>                .SpecialCells(xlFormulas, xlErrors).FormulaR1C1 = _<br>                    "=RC[1]"<br>                .Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Columns(DataCol).Offset(, 2).Resize(, 2).Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Here is my test data and results.

Excel Workbook
AB
1DataRank
252
372
435
581
652
744
891
972
1044
1135
12
13
Rank Alternate Rows (2)
 
Last edited:
Upvote 0
Hi Peter,
Thank you so much for the reply - I'll test it out when I'm in the office tomorrow. And let you know.
Just two little things, my version has to be more complex in that:
- Instead of there being two ranges, there is going to be 42 each 200 cells apart and consisting of 300 total cells in each range. I'll just have to Dim more elements, i.e just build on what you've written.
- If I need to do a similar thing with a PercentRank, do I just switch Rank with PercentRank.
Thank you so much for your help,
Alex.
 
Upvote 0
Hi Peter, the macro works - but it breaks down when I input negative values. Any ideas?
Thanks,
Alex
 
Upvote 0
but it breaks down when I input negative values.
"breaks down" is a bit vague. ;)
Can you be more specific.

This is what I got. What is wrong with it?

Excel Workbook
AB
1DataRank
251
3-73
4-24
581
651
7-85
8-5.25
9-73
1043
1132
12
Rank Alternate Rows (3)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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
Back
Top