Ranking non-contiguous rows of data

[HJC]

I have three columns of data.
Column A is a players name.
Column B is a team name.
Column C is a score.

In column D - I want to rank the score of each player against only those players on the same team. Is this possible without sorting the rows by team name? Thank you.

[Chris Davison]

there is probably a more elegant way, but you could name your ranges of scores as they belong to each team, so ateam, bteam, cteam etc etc and then do some IF statements :

Microsoft Excel - HJC.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
H9		=
*	A	B	C	D	E
1	Name	Team	Score	Rank	*
2	John	A	10	:alert('=IF(B2="A",RANK(C2,ascore,0),IF(B2="B",RANK(C2,bscore,0),IF(B2="C",RANK(C2,cscore,0),0)))')>6	*
3	Paul	B	11	:alert('=IF(B3="A",RANK(C3,ascore,0),IF(B3="B",RANK(C3,bscore,0),IF(B3="C",RANK(C3,cscore,0),0)))')>5	*
4	George	C	45	:alert('=IF(B4="A",RANK(C4,ascore,0),IF(B4="B",RANK(C4,bscore,0),IF(B4="C",RANK(C4,cscore,0),0)))')>3	*
5	Ringo	A	32	:alert('=IF(B5="A",RANK(C5,ascore,0),IF(B5="B",RANK(C5,bscore,0),IF(B5="C",RANK(C5,cscore,0),0)))')>4	*
6	John2	B	61	:alert('=IF(B6="A",RANK(C6,ascore,0),IF(B6="B",RANK(C6,bscore,0),IF(B6="C",RANK(C6,cscore,0),0)))')>1	*
7	Paul2	C	51	:alert('=IF(B7="A",RANK(C7,ascore,0),IF(B7="B",RANK(C7,bscore,0),IF(B7="C",RANK(C7,cscore,0),0)))')>2	*
8	George2	A	51	:alert('=IF(B8="A",RANK(C8,ascore,0),IF(B8="B",RANK(C8,bscore,0),IF(B8="C",RANK(C8,cscore,0),0)))')>2	*
9	Ringo2	B	23	:alert('=IF(B9="A",RANK(C9,ascore,0),IF(B9="B",RANK(C9,bscore,0),IF(B9="C",RANK(C9,cscore,0),0)))')>2	*
10	John3	C	100	:alert('=IF(B10="A",RANK(C10,ascore,0),IF(B10="B",RANK(C10,bscore,0),IF(B10="C",RANK(C10,cscore,0),0)))')>1	*
11	Paul3	A	43	:alert('=IF(B11="A",RANK(C11,ascore,0),IF(B11="B",RANK(C11,bscore,0),IF(B11="C",RANK(C11,cscore,0),0)))')>3	*
12	George3	B	16	:alert('=IF(B12="A",RANK(C12,ascore,0),IF(B12="B",RANK(C12,bscore,0),IF(B12="C",RANK(C12,cscore,0),0)))')>4	*
13	Ringo3	C	17	:alert('=IF(B13="A",RANK(C13,ascore,0),IF(B13="B",RANK(C13,bscore,0),IF(B13="C",RANK(C13,cscore,0),0)))')>5	*
14	John4	A	18	:alert('=IF(B14="A",RANK(C14,ascore,0),IF(B14="B",RANK(C14,bscore,0),IF(B14="C",RANK(C14,cscore,0),0)))')>5	*
15	Paul4	B	22	:alert('=IF(B15="A",RANK(C15,ascore,0),IF(B15="B",RANK(C15,bscore,0),IF(B15="C",RANK(C15,cscore,0),0)))')>3	*
16	George4	C	33	:alert('=IF(B16="A",RANK(C16,ascore,0),IF(B16="B",RANK(C16,bscore,0),IF(B16="C",RANK(C16,cscore,0),0)))')>4	*
17	Ringo4	A	999	:alert('=IF(B17="A",RANK(C17,ascore,0),IF(B17="B",RANK(C17,bscore,0),IF(B17="C",RANK(C17,cscore,0),0)))')>1	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[Yogi Anand]

Hi Chris:
Good practical approach in regard to assigning range names to the team scores. If I may, I used the following shorter formula in cells D2 through D11:

=RANK(C2:C10,IF(B2="a",ascore,IF(B2="b",bscore,cscore)))

Regards!

[Chris Davison]

Yogi,

I like it ! there's more to it than first meets the eye

Nice

[Aladin Akyurek]

On 2002-05-19 16:41, HJC wrote:
I have three columns of data.
Column A is a players name.
Column B is a team name.
Column C is a score.

In column D - I want to rank the score of each player against only those players on the same team. Is this possible without sorting the rows by team name? Thank you.

Lets say that A1:C17 houses the sample Chris provided:

{"Name","Team","Score";
"John","A",10;
"Paul","B",11;
"George","C",45;
"Ringo","A",32;
"John2","B",61;
"Paul2","C",51;
"George2","A",51;
"Ringo2","B",23;
"John3","C",100;
"Paul3","A",43;
"George3","B",16;
"Ringo3","C",17;
"John4","A",18;
"Paul4","B",22;
"George4","C",33;
"Ringo4","A",999}

In E1 enter:

=MATCH(9.99999999999999E+307,C:C)

In D2 array-enter and copy down for all players:

=MATCH(C2,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B2,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT("1:"&$E$1))),0)

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

The figure below shows the data and the results;

Microsoft Excel - aaRankIf HJC.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
G27 =
	A	B	C	D	E
1	Name	Team	Score	Rank	:alert('=MATCH(9.99999999999999E+307,C:C)')>17
2	John	A	10	:alert('{=MATCH(C2,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B2,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>6
3	Paul	B	11	:alert('{=MATCH(C3,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B3,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>5
4	George	C	45	:alert('{=MATCH(C4,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B4,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>3
5	Ringo	A	32	:alert('{=MATCH(C5,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B5,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>4
6	John2	B	61	:alert('{=MATCH(C6,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B6,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>1
7	Paul2	C	51	:alert('{=MATCH(C7,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B7,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>2
8	George2	A	51	:alert('{=MATCH(C8,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B8,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>2
9	Ringo2	B	23	:alert('{=MATCH(C9,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B9,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>2
10	John3	C	100	:alert('{=MATCH(C10,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B10,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>1
11	Paul3	A	43	:alert('{=MATCH(C11,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B11,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>3
12	George3	B	16	:alert('{=MATCH(C12,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B12,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>4
13	Ringo3	C	17	:alert('{=MATCH(C13,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B13,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>5
14	John4	A	18	:alert('{=MATCH(C14,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B14,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>5
15	Paul4	B	22	:alert('{=MATCH(C15,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B15,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>3
16	George4	C	33	:alert('{=MATCH(C16,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B16,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>4
17	Ringo4	A	999	:alert('{=MATCH(C17,LARGE(IF(OFFSET($B$2,0,0,$E$1,1)=B17,OFFSET($C$2,0,0,$E$1,1)),ROW(INDIRECT(%221:%22&$E$1))),0)}')>1
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.26]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo



If you can sort your data on the Team column, another non-array approach to the problem is also possible.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-05-20 06:57 ]