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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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]
 

alocke

Board Regular
Joined
Nov 10, 2011
Messages
64
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.
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

do you mean RANK all even rows with one ranking and all odd cells with another ranking ???
 

alocke

Board Regular
Joined
Nov 10, 2011
Messages
64
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

alocke

Board Regular
Joined
Nov 10, 2011
Messages
64
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.
 

alocke

Board Regular
Joined
Nov 10, 2011
Messages
64
Hi Peter, the macro works - but it breaks down when I input negative values. Any ideas?
Thanks,
Alex
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,841
Members
414,342
Latest member
K Darrell Smith

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
Top