Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 21st 06, 12:43 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 28
Default 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.





  #2   Report Post  
Old August 21st 06, 02:29 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 459
Default Index function and changing criteria help.

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.



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

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy


  #3   Report Post  
Old August 21st 06, 03:53 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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.


  #4   Report Post  
Old August 21st 06, 01:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 28
Default 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.


  #5   Report Post  
Old August 22nd 06, 04:24 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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 B11 : 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 B22, 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 B22 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
---




  #6   Report Post  
Old August 22nd 06, 07:37 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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
---




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing worksheet cells from within a function James4U2enjoy Setting up and Configuration of Excel 1 October 14th 05 02:16 PM
Index function works in A2, but not in A10, Why is that? Cant figure it out. sonar Excel Worksheet Functions 8 August 28th 05 07:44 PM
changing font style in a complex worksheet function gvm Excel Worksheet Functions 6 August 3rd 05 01:29 AM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Can I use TODAY Function in formula without it changing the next . Tucson Guy Excel Discussion (Misc queries) 1 December 19th 04 09:47 AM


All times are GMT +1. The time now is 06:33 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017