Excel Ranking Macro 1

[Queryman]

I often have to create ranks in Excel and it's time consuming when there are multiple rows of ranks based on Regional performance versus Divisional performance. I was wondering whether anyone has some code that they could share that would help in this process. I am thinking that if the code will be able to create ranks based on a sorted column's values, but start re-ranking every time the column value changes, it would be most useful.
I found some code on the web, not sure how to modify this to suit my needs

Sub SomewhatfasterRanking()
    Application.ScreenUpdating = False
    C = Selection.CurrentRegion.Columns.Count
    Selection.CurrentRegion.Offset(0, C).Select
    With Selection
        .Value = "=RANK(RC[-5],R1C[-5]:R96C[-5])"
        .Copy
        .PasteSpecial Paste:=xlValues
    End With
End Sub

TIA,



Michael

 

[TonyJollans]

Hi Michael,

I'm not entirely sure (a) what you want or (b) what you have at the moment, but all that code does is use the Worksheet RANK function and then hide the fact that it's done so. Is there any reason why you can't just code RANK formulae in the worksheet - I doubt you'll write code that's more efficient.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

so what are your needs and how does the code not work for you ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Queryman]

It's easy to create a rank formula and copy down, but I have to do two ranks, one for territories in a region which say for example is 500 entries and they have 50 divisions, I need to create a seperate rank for each territory within a division and each division might have five to ten territories. So it's not easy to copy that formula down, I am thinking that if the code will be able to create ranks based on a sorted column's values, but start re-ranking every time the column value changes, it would be most useful.




Michael

 

[xlbo]

The following will rank bsaed on 2 variables:

=RANK(B2,INDIRECT("B" & MATCH($A2,$A$2:$A$19,0) & ":B" & MATCH($A2,$A$2:$A$19,1)+1))

where Variable 1 is in A1:A19 and variable 2 (to be ranked WITHIN variable 1) is in column B

It is a simple RANK formula which uses the 3rd argument of the MATCH function to fin the 1st and last position of each variable in column A - it then ranks the comparative values in column B within column A

the 3rd argument of 0 finds the 1st match and the 3rd argument of 1 finds the 2nd last match (hence the +1)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Queryman]

Thanks, i will try this



Michael

 

[Queryman]

I must not understand how to use this because I get #N/A.

For region rank formula I have
=RANK(AI2,$AI$2:$AI$351)

For division rank formula I had before change
=RANK(AI2,$AI$2:$AI$10)

after change
=RANK(AI2,INDIRECT("B" & MATCH($AI2,$AI$2:$AI$351,0) & ":B" & MATCH($AI2,$AI$2:$AI$351,1)+1))

The region names are in column A, the divsion names are in column b



Michael

 

[xlbo]

Please show an example of your data layout...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Queryman]

COL     
A	Region
B	Division
C	Territory
AH	Total Parts
AI	Total Sales $
AJ	Region $ Rank 
AK	Division $ Rank

Michael

 

[xlbo]

Ranking for Total Sales by Division IN Region:

=RANK(AI2,INDIRECT("AI" & MATCH($A2,$A$2:$A$351,0) & ":AI" & MATCH($A2,$A$2:$A$351,1)+1))

where data is as you have indicated - assuming headers in row 1 and data starting from row 2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Queryman]

I have managed to confuse everyone including myself, I apologize.

I am ranking territories with a region by their sales and also ranking territories within a division by their sales

Your formula works, but it is giving me ranks I don't quite follow.
Following is my ranks arrived for row 2 using formulas mentioned in my previous post and what I got using yours.

Region $ Rank 30
Division $ Rank 1
Your Rank 21

Total Sales for that line was $723,049. I should point out that total sales all regions was 127,192,577 and sales for 1st division was 2,738,173



Michael

 

[xlbo]

Michael - I am indeed confused !!

Can you send me a small sample layout of your data by email ? preferably with a manual ranking formula that you have used in it as well

Figures can be real or made up if they are sensitive

Geoff <dot> Barraclough at PunchTaverns <dot> com

When I have seen the data layout etc, I should be able to post back with a solution

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Hokey Cokey here we go

I assumed that everything was sorted in ascending order - obviously that may not be the case when you have 3 seperate hierarchy structures there. The following provides the same rank as your example file but does rely on all regions / territories being grouped together (as per your layout)rather than scattered in random order (that's just a caveat in case you wondered ;-) )

=RANK(AI2,INDIRECT("AI" & MATCH($B2,$B$1:$B$351,0) & ":AI" & MATCH($B2,$B$1:$B$351,0)+COUNTIF($B$1:$B$351,B2)-1))

The 1st MATCH formula finds the 1st instance of the Division
The COUNTIF adds the number of instances of the Division to the 1st match to get the last row dynamically - it then just ranks between 1st row and last row - the -1 is to account for the COUNTIF including the 1st match.

Basically, where you see "AI" - that needs to be the value that is being ranked
Where you see "B" - that needs to be the PARENT group to be ranked WITHIN (in this case Territory WITHIN region)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Queryman]

You are a genius, works perfectly!!!!!



Michael

 

[xlbo]

No probs - gonna keep that one for my own use as well - I often have to do similar stuff !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question