Thanks:  0
Likes:  0

# Thread: Ranking data in arrays

1. Is it possible to rank data in an array? For example, the array is Height (a,b). I would like to create an array HeightRank (a,b) whereby all of the people in array a=1 would be ranked, etc. So, HeightRank (1,3) would be the rank of b=3 among all those with a=1. a represents a group and b a subgroup of each a. I want to rank b's within their group. I am not interested in inputing the array values in a spreadsheet and using the RANK function (I know how to do that) but need VBA code to do the same thing.

Thanks.

[ This Message was edited by: tanda on 2002-03-12 13:03 ]

[ This Message was edited by: tanda on 2002-03-12 13:04 ]

2. Can you give some kind of example of the data and then of what you want your array to look like?

Thank you,

rh

3. The arrays contain numbers. They are two-dimensional. For example, BattingAverage (t,p). t represents the number of teams and p represents the players on each team. However, the teams and players are represented by numbers. So t=1 may be the Yankees, etc. BattingAverage (1,1) may be Derek Jeter (since Jeter is p=1 when t=1). Thus, every player in the league has a batting average assigned to it which can be represented by the appropriate BattingAverage (t,p).

Note: my use is not for batting averages or baseball, this is just an example, it is for others type of statistics. In my actual use, t= 10 or so and p = 10 or so with there being approximately 100 distinct units with a statistic assigned to it.

So, I want to rank a player's batting average within his group with the group being his team. Thus, I want to rank the BattingAverage (1,1), (1,2), (1,3) etc. If Derek Jeter (1,1) had the highest batting average on his team (as defined by t=1) then RankBattingAverage (1,1) = 1 would be desired result.

I hope this makes sense.

Thanks.

4. On 2002-03-12 14:40, tanda wrote:
The arrays contain numbers. They are two-dimensional. For example, BattingAverage (t,p). t represents the number of teams and p represents the players on each team. However, the teams and players are represented by numbers. So t=1 may be the Yankees, etc. BattingAverage (1,1) may be Derek Jeter (since Jeter is p=1 when t=1). Thus, every player in the league has a batting average assigned to it which can be represented by the appropriate BattingAverage (t,p).

Note: my use is not for batting averages or baseball, this is just an example, it is for others type of statistics. In my actual use, t= 10 or so and p = 10 or so with there being approximately 100 distinct units with a statistic assigned to it.

So, I want to rank a player's batting average within his group with the group being his team. Thus, I want to rank the BattingAverage (1,1), (1,2), (1,3) etc. If Derek Jeter (1,1) had the highest batting average on his team (as defined by t=1) then RankBattingAverage (1,1) = 1 would be desired result.

I hope this makes sense.

Thanks.

Not really. Usually you would actually re-sort the array. Can you give a better example of your data? A 2-dimensional array is just like an Excel Spreadsheet (say dimension 1 is rows and 2 is columns).

So do you have something like:

Jeter .300
Giambi .330

?

or

.300 Jeter
.330 Giambi

...and how do you want them to look when you are done?

5. That was me - don't know why it was anon.

6. On 2002-03-12 13:02, tanda wrote:
Is it possible to rank data in an array? For example, the array is Height (a,b). I would like to create an array HeightRank (a,b) whereby all of the people in array a=1 would be ranked, etc. So, HeightRank (1,3) would be the rank of b=3 among all those with a=1. a represents a group and b a subgroup of each a. I want to rank b's within their group. I am not interested in inputing the array values in a spreadsheet and using the RANK function (I know how to do that) but need VBA code to do the same thing.

Thanks.

[ This Message was edited by: tanda on 2002-03-12 13:03 ]

[ This Message was edited by: tanda on 2002-03-12 13:04 ]
Not sure I understood the problem fully, but it sounds like you want to do conditional ranking (or "RankIf"). For a non-VBA approach, the following could be of some value to you:

http://www.mrexcel.com/wwwboard/messages/13717.html
http://www.mrexcel.com/wwwboard/messages/13984.html
http://www.mrexcel.com/wwwboard/messages/14101.html

7. I have an Number (a,b).

Number (a,b) equals some value expressed as a number for each a and b.

I need to rank each value within a.

So, if Number (4,5) = 0.2, then I want to rank that value among all values when a = 4.

Maybe this will help.

Number (4,1) = 0.3
Number (4,2) = 0.5
Number (4,3) = 0.1
Number (4,4) = 0.9
Number (4,5) = 0.2

There are no other values when a = 4. I want to rank these values. This is a simplified example.

The result I would like is:

Rank (4,1) = 3
Rank (4,2) = 2
Rank (4,3) = 5
Rank (4,4) = 1
Rank (4,5) = 4

Again, I can do it by importing the values to a spreadsheet and using the RANK function, but for a variety of reasons, I would like a VBA solution.

[ This Message was edited by: tanda on 2002-03-12 16:44 ]

8. If you tried to do what you said, you would lose your 0.2, etc. values, and would be left with 1, 2, 3, 4, 5 (etc.). Do you not want those values in your array any longer?

How about if you sorted the array, so that your results looked like this:

Rank (4,1) = 0.9
Rank (4,2) = 0.5
Rank (4,3) = 0.3
Rank (4,4) = 0.2
Rank (4,5) = 0.1

?

I think that is what you're looking for...let me know if not.

9. Don't know why that last one was anon - was me.

10. On 2002-03-13 08:50, Russell Hauf wrote:
Don't know why that last one was anon - was me.
When you are not logged on, you become Anonymous.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•