Index function and changing criteria help.
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. |
Index function and changing criteria help.
|
Index function and changing criteria help.
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(LARG E($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: 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. |
Index function and changing criteria help.
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 Max wrote: 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(LARG E($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: 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. |
Index function and changing criteria help.
wrote
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. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Index function and changing criteria help.
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(Gam e,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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
All times are GMT +1. The time now is 05:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com