Index function and changing criteria help.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

conorfinnegan@gmail.com - 21 Aug 2006 00:43 GMT

I have 9000 rows of data and I would like to use the index function to
list the top 10 or first 10 values for each of my 35 categories. My
data is listed out as follows. (Please note that Player 1 for baseball
is not the same player 1 for football)

Baseball Player 1 5
Baseball Player 2 3
Baseball Player 3 2
Baseball Player 4 6
Baseball Player 5 1
Football Player 1 20
Football Player 2 30
Football Player 3 15
Football Player 4 18
Football Player 5 31

So then my hope is to have another sheet that draws from this data and
puts it in order while indexing and offsetting the following column -
not as big a deal. It should look something like this: (only top 3 for
this example)

Baseball
Player 4 6
Player 1 5
Player 2 3

Football Player 5 31
Player 2 30
Player 1 20

I have changed the data to keep it simple. I do not have 9000 rows of
this crap. As I mentioned, I would like to show the top 10
corresponding values. I can sort the data on the sheet to sort column
a and then column c to put everything in order from the beginning if it
is too difficult. I tried using the index function on the data and I
get the baseball value to get me what I want but then it gets to
football and can't do it because the part that changes the row not says
1:10, etc.

Any help would be greatly appreciated. I know a pivot table works for
this so please don't suggest that as a solution. Thanks for your help
in advance, whoever you are.

Reply to this Message

Franz Verga - 21 Aug 2006 02:29 GMT

> I have 9000 rows of data and I would like to use the index function to
> list the top 10 or first 10 values for each of my 35 categories. My
[quoted text clipped - 38 lines]
> this so please don't suggest that as a solution. Thanks for your help
> in advance, whoever you are.

I think you could use the RANK function. Check the online help

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Max - 21 Aug 2006 03:53 GMT

Here's a set-up using non-array formulas which provides the "Extract top xx
by game selected" functionality in a new sheet. The possibility of
ties/multiple ties in scores amongst the top xx is catered for in the set-up.

A sample construct is available at:
http://cjoint.com/?ivePYQz57f
Extract Top xx by Game in new sht.xls
(Link above is good for 2 weeks)

Assuming source data in sheet: X, cols A to C, data in row2 down. The key
cols are col A ie the game (Baseball, Football ..), and col C = scores.
Source data in X can be in any order (need not be sorted)

In another sheet: Y (say), place

In A2:
=IF(X!A2="","",IF(COUNTIF(X!$A$2:A2,X!A2)>1,"",ROW()))

In B2:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(X!A:A,MATCH(SMALL(A:A,ROW(A1)),A:A,0)))

In C2:
=IF(X!A2="","",IF(X!A2=$D$1,X!C2-ROW()/10^10,""))

Select A2:C2, copy down as far as required to cover the max expected extent
of data in X, say down to C9000? (Hide away cols A to C, or just format the
font in white to mask)

Click Insert > Name > Define, input:
Names in workbook: Game
Refers to: =OFFSET(Y!$B$2,,,SUMPRODUCT(--(Y!$B$2:$B$2000<>"")))
Click OK

Then select D1, click Data > Validation, Allow: List, Source: =Game
D1 will now yield a selectable dropdown of unique games from the key col A
in X

Enter the col headers in E1:F1 : Player, Score

Then place in E2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(X!B:B,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))

Copy E2 to F2, fill down to say F20 to cover the possibility of ties /
multiple ties in scores amongst the top 10. If there's absolutely no
possibility of ties in the scores for the top 10, then just fill down by 10
rows to F11

Test it out, select a game from the DV droplist in D1. The list of players
with the highest scores for the selected game will display in descending
order within the filled range E2:F15, all neatly bunched at the top. Just
read-off the top "10" as desired. In the event of tied scores, players and
their scores will be listed in the same relative order as they appear in X.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

conorfinnegan@gmail.com - 21 Aug 2006 13:36 GMT

Max,
The sheet you built is very impressive. I wouldn't have even come
close to that. My question for you now is: Is it possible to have it
so I have baseball, then 10 rows down or so, football and all the
information for everything shows up on the same page? I like the drop
down list idea, but it may not work for me. Please let me know your
thoughts when you can. Thanks.

Conor

> Here's a set-up using non-array formulas which provides the "Extract top xx
> by game selected" functionality in a new sheet. The possibility of
[quoted text clipped - 96 lines]
> > this so please don't suggest that as a solution. Thanks for your help
> > in advance, whoever you are.

Reply to this Message

Max - 22 Aug 2006 04:24 GMT

> Max,
> The sheet you built is very impressive. I wouldn't have even come
[quoted text clipped - 3 lines]
> down list idea, but it may not work for me. Please let me know your
> thoughts when you can. Thanks.

Here's some thoughts ..

A sample construct is available at:
http://cjoint.com/?iwfs2J6Ggd
Auto-List 1st xx by game in new sht.xls
(Link above is good for 2 weeks)

Assume source data in sheet: X, cols A to C, data within A2:C9000.
The key cols are col A ie the game, and col C = scores (Col B = Players)

Assume the 35 unique categories are listed in a defined range: Game
=X!$N$3:$N$37
[Presume the 35 uniques list is at hand. Or just use advanced filter >
uniques on col A to extract the list]

In another sheet: Y (say), paste the col labels in B1:D1 : Game, Player,
Score

Put in A2:
=IF(X!A2="","",X!C2-ROW()/10^10)
Copy A2 down to A9000 to cover the max expected extent of data in X (Leave
A1 empty)

Put in B2:
=IF(MOD(ROW(A1)-1,10)<>0,"",IF(ISERROR(INDEX(Game,INT((ROW(A1)-1)/10)+1)),"",IF(INDEX(Game,INT((ROW(A1)-1)/10)+1)=0,"",INDEX(Game,INT((ROW(A1)-1)/10)+1))))

Put in C2, array-enter the formula, ie press CTRL+SHIFT+ENTER (instead of
just pressing ENTER):
=IF(INDEX(Game,INT((ROW(A1)-1)/10)+1)=0,"",INDEX(X!B$2:B$9000,MATCH(LARGE(IF(X!$A$2:$A$9000=INDEX(Game,INT((ROW(A1)-1)/10)+1),$A$2:$A$9000),MOD(ROW(A1)-1,10)+1),$A$2:$A$9000,0)))
Copy C2 to D2

Select B2:D2, fill down by 350 rows (as 35 categories x 10 rows each = 350
rows)

Cols B to D will yield the required results. The listing of all the 35
categories will appear in col B (only in the 1st cell, in steps of 10), with
the 1st 10 players and scores listed in cols C & D in descending order by
scores. Tied score cases within the 1st 10, if any, within any category,
will be listed in the same relative order that these appear in X. Hide away
col A (arb tiebreaker col), or just format the font in white to mask.

Adapt to suit. Change the "10" in the MOD(...) & INT(...) parts to "20" in
the formulas in B2:D2 if you want to list the 1st 20 for each category, Then
fill down correspondingly by 700 rows (as 35 categories x 20 rows each = 700
rows). Take care to re-array-enter the formula in C2 whenever you edit.
Ensure this by viewing within the formula bar that the curly braces { } are
inserted by Excel as proof that the array-entering is done correctly before
copying C2 across to D2 and filling down.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 22 Aug 2006 07:37 GMT

In the earlier set-up, #NUM! error lines in cols C and D will appear for any
of the 35 categories with less than 10 players/scores, once all the
player/scores are exhausted in the extract. To suppress this error in-place,
ie make it return "blanks", viz: "" [without using conditional formatting to
mask] ..

Put instead in C2, array-enter the formula, ie press CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(OR(INDEX(Game,INT((ROW(A1)-1)/10)+1)=0,ISERROR(LARGE(IF(X!$A$2:$A$9000=INDEX(Game,INT((ROW(A1)-1)/10)+1),$A$2:$A$9000),MOD(ROW(A1)-1,10)+1))),"",INDEX(X!B$2:B$9000,MATCH(LARGE(IF(X!$A$2:$A$9000=INDEX(Game,INT((ROW(A1)-1)/10)+1),$A$2:$A$9000),MOD(ROW(A1)-1,10)+1),$A$2:$A$9000,0)))
Copy C2 to D2

(Rest of construct unchanged)

Here's a working sample* to illustrate:
http://cjoint.com/?iwixY3UKXm
Auto-List_1st_xx_by_game_in_new_sht_AddErrTrap.xls
*source data in X intentionally amended to contain less than 10 items per
category
(Link is good for 14 days)

If the above event is unlikely, then just stick with the former, shorter
version in cols C and D for optimal calc performance

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message