Matching columns

[baseball]

I have an Excel 97 database in which I track 'games scores' for pitchers. That is a calculated field, and there can be duplicate scores. I want to produce a dynamic list of the top-10 game scores and the associated pitcher, so when I add new records, the list updates.

There is no problem with the games scores themselves. In cells C2:C11 I have the numbers 1 through 10, then copy this formula in cells D2:D11

=LARGE(M$22:M$300,C2)
=LARGE(M$22:M$300,C3)
etc.

I have been using the following formulas in cells E2:E11 to match the pitcher's name to the corresponding game score, but it does not work when there are duplicate games scores; it uses the first pitcher it finds for any given score.

=INDEX($D:$D,MATCH(LARGE(D:D,C2),M:M,0))
=INDEX($D:$D,MATCH(LARGE(D:D,C3),M:M,0))
etc.

The result look like this:

Rank GameScore Pitcher
1 92 D_Lowe
2 86 P_Martinez
3 84 P_Martinez
4 82 F_Castillo
5 79 P_Martinez
6 77 D_Lowe
7 75 D_Oliver
8 75 D_Oliver
9 73 D_Lowe
10 71 J_Burkett

However, for the two game scores of 75, one record should be Pitcher=P_Martinez and the other should be D_Oliver. Can anyone help me sort this out?

Thanks,
Cliff

[Aladin Akyurek]

The result look like this:

Rank GameScore Pitcher
1 92 D_Lowe
2 86 P_Martinez
3 84 P_Martinez
4 82 F_Castillo
5 79 P_Martinez
6 77 D_Lowe
7 75 D_Oliver
8 75 D_Oliver
9 73 D_Lowe
10 71 J_Burkett

Cliff,

Care to post the data from which the above result list is constructed?

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-05-20 07:12 ]

[baseball]

On 2002-05-20 07:11, Aladin Akyurek wrote:
The result look like this:

Rank GameScore Pitcher
1 92 D_Lowe
2 86 P_Martinez
3 84 P_Martinez
4 82 F_Castillo
5 79 P_Martinez
6 77 D_Lowe
7 75 D_Oliver
8 75 D_Oliver
9 73 D_Lowe
10 71 J_Burkett

Cliff,

Care to post the data from which the above result list is constructed?

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-05-20 07:12 ]

Aladin,

I thought that you could just take it from the result I lisdted, but here is some actual data with a duplicate game score with different pitchers.

G BOSTON...... ip. h r er bb so hr era. GS
1 P_Martinez.. 3.0 9 8 7 2 4 0 9.00 13
2 D_Lowe...... 7.0 1 0 0 3 1 0 0.00 73
3 F_Castillo.. 6.0 1 1 1 1 3 1 1.50 68
4 P_Martinez., 6.0 3 1 0 1 5 0 0.00 68
5 T_Wakefield. 5.0 5 4 4 0 4 1 7.20 45
6 D_Lowe...... 7.0 8 2 2 1 3 0 2.57 55
7 F_Castillo.. 6.0 3 2 2 3 4 1 3.00 59
8 D_Oliver.... 5.2 5 0 0 3 4 0 0.00 60

If you just list the top five, then the output should be

73 D_Lowe
68 F_Castillo
68 P_Martines
60 D_Oliver
59 F_Castillo

Thanks,
Cliff

[Aladin Akyurek]

Cliff,

To save space I omit the columns between pitchers and scores. Lets assume that these are in columns A and B.

In C2 enter and copy down:

=RANK(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1

In E2 enter and copy down:

=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)

The last formula can also be entered in a diffrent worksheet than the one that houses the data.

The figure below shows these formulas in action:

Microsoft Excel - aaTopPitchers Baseball.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
A1 = G BOSTON
	A	B	C	D	E
1	G BOSTON	GS	Rank
2	P_Martinez	13	:alert('=RANK(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1')>8		:alert('=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)')>D_Lowe
3	D_Lowe	73	:alert('=RANK(B3,$B$2:$B$9)+COUNTIF($B$2:B3,B3)-1')>1		:alert('=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)')>F_Castillo
4	F_Castillo	68	:alert('=RANK(B4,$B$2:$B$9)+COUNTIF($B$2:B4,B4)-1')>2		:alert('=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)')>P_Martinez
5	P_Martinez	68	:alert('=RANK(B5,$B$2:$B$9)+COUNTIF($B$2:B5,B5)-1')>3		:alert('=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)')>D_Oliver
6	T_Wakefield	45	:alert('=RANK(B6,$B$2:$B$9)+COUNTIF($B$2:B6,B6)-1')>7		:alert('=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)')>F_Castillo
7	D_Lowe	55	:alert('=RANK(B7,$B$2:$B$9)+COUNTIF($B$2:B7,B7)-1')>6
8	F_Castillo	59	:alert('=RANK(B8,$B$2:$B$9)+COUNTIF($B$2:B8,B8)-1')>5
9	D_Oliver	60	:alert('=RANK(B9,$B$2:$B$9)+COUNTIF($B$2:B9,B9)-1')>4
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


Aladin

ADDENDUM.

The formula in E2:

=INDEX(A:A,MATCH(ROW()-1,$C$2:$C$9,0)+1)

can be also expressed as:

=INDEX(A:A,MATCH(ROW(1:1),$C$2:$C$9,0)+1)

The change is in order to avoid difficulties some users have in adjusting the -1 bit.

[ This Message was edited by: Aladin Akyurek on 2002-08-07 17:14 ]