Ranking data in arrays

[tanda]

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 ]

[Russell Hauf]

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

Thank you,

rh

[tanda]

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.

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?

[Russell Hauf]

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

[Aladin Akyurek]

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

Aladin

[tanda]

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 ]

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.

[Russell Hauf]

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

[Aladin Akyurek]

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.

Aladin