Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
I would like to be able to set up an extra tab in my worksheet that gives me
the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
the rank() or large() function would identify the top ten people
then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Sorry to be rude but how do i do that?
"bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Hi,
Using large with your date liad out like this:- Col A Col B Name 1 23 Name 2 56 Name 3 33 Name 4 44 Name 5 11 Name 6 88 Put this formula on your sheet and drag down as far as you want. Change the ranges as required. =INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$ 1:B$100,0)) Mike "louiscourtney" wrote: Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Mike
Thanks for the reply Sorry but I'm quite a novice The data i want to extract from a tab called "sick history" is in columns A3 down to A220 and then P3 down to P220 and then to auotmatically place it in a new tab called Top 10 and then sort it in order highest first Is there away to do this?? "Mike H" wrote: Hi, Using large with your date liad out like this:- Col A Col B Name 1 23 Name 2 56 Name 3 33 Name 4 44 Name 5 11 Name 6 88 Put this formula on your sheet and drag down as far as you want. Change the ranges as required. =INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$ 1:B$100,0)) Mike "louiscourtney" wrote: Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
I assume you mean your names are in A3 to A220 and their absences are in P2
to P220 on the sheet Sick List. Paste this anywhere on your sheet Top Ten and drag down 10 cells. =INDEX('Sick History'!A$3:A$220,MATCH(LARGE('Sick History'!P$3:P$220,ROW(A1)),'Sick History'!P$3:P$220,0)) Mike "louiscourtney" wrote: Mike Thanks for the reply Sorry but I'm quite a novice The data i want to extract from a tab called "sick history" is in columns A3 down to A220 and then P3 down to P220 and then to auotmatically place it in a new tab called Top 10 and then sort it in order highest first Is there away to do this?? "Mike H" wrote: Hi, Using large with your date liad out like this:- Col A Col B Name 1 23 Name 2 56 Name 3 33 Name 4 44 Name 5 11 Name 6 88 Put this formula on your sheet and drag down as far as you want. Change the ranges as required. =INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$ 1:B$100,0)) Mike "louiscourtney" wrote: Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Using Mike's reply:
=INDEX('Sick history'!A$3:A$220,MATCH(LARGE('Sick history'P3$1:P$220,ROW(A1)),'Sick history'p$3:p220,0)) Place in "Top 10" and copy down. "louiscourtney" wrote: Mike Thanks for the reply Sorry but I'm quite a novice The data i want to extract from a tab called "sick history" is in columns A3 down to A220 and then P3 down to P220 and then to auotmatically place it in a new tab called Top 10 and then sort it in order highest first Is there away to do this?? "Mike H" wrote: Hi, Using large with your date liad out like this:- Col A Col B Name 1 23 Name 2 56 Name 3 33 Name 4 44 Name 5 11 Name 6 88 Put this formula on your sheet and drag down as far as you want. Change the ranges as required. =INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$ 1:B$100,0)) Mike "louiscourtney" wrote: Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Hi Mike,
Your formula falls down if there are two values the same in the second column. Using your original example if Name 3 and Name 4 both equal 33, then the formula will return Name 3 twice and ignore Name 4. Martin "Mike H" wrote in message ... I assume you mean your names are in A3 to A220 and their absences are in P2 to P220 on the sheet Sick List. Paste this anywhere on your sheet Top Ten and drag down 10 cells. =INDEX('Sick History'!A$3:A$220,MATCH(LARGE('Sick History'!P$3:P$220,ROW(A1)),'Sick History'!P$3:P$220,0)) Mike "louiscourtney" wrote: Mike Thanks for the reply Sorry but I'm quite a novice The data i want to extract from a tab called "sick history" is in columns A3 down to A220 and then P3 down to P220 and then to auotmatically place it in a new tab called Top 10 and then sort it in order highest first Is there away to do this?? "Mike H" wrote: Hi, Using large with your date liad out like this:- Col A Col B Name 1 23 Name 2 56 Name 3 33 Name 4 44 Name 5 11 Name 6 88 Put this formula on your sheet and drag down as far as you want. Change the ranges as required. =INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$ 1:B$100,0)) Mike "louiscourtney" wrote: Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Assuming that the names are in A1:A26, score in B1:B26 on sheet 1, add this
to C1 and copy down =RANK(B1,$B$1:$B$26)+COUNTIF($B$1:$B1,B1)-1 Select A1:A10 on sheet 2 and eneter this in the FORMULA BAR =INDEX(Sheet1!$A$1:$A$26,MATCH(ROW(INDIRECT("1:10" )),Sheet1!$C$1:$C$26,0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "louiscourtney" wrote in message ... Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Toppers & Mike
Thanks for you help Works perfectly, just one last point I how have the names of the top 10 people is there anyway to have it so it shows the name in one column and then the score in the next column?? Thanks in advance "Toppers" wrote: Using Mike's reply: =INDEX('Sick history'!A$3:A$220,MATCH(LARGE('Sick history'P3$1:P$220,ROW(A1)),'Sick history'p$3:p220,0)) Place in "Top 10" and copy down. "louiscourtney" wrote: Mike Thanks for the reply Sorry but I'm quite a novice The data i want to extract from a tab called "sick history" is in columns A3 down to A220 and then P3 down to P220 and then to auotmatically place it in a new tab called Top 10 and then sort it in order highest first Is there away to do this?? "Mike H" wrote: Hi, Using large with your date liad out like this:- Col A Col B Name 1 23 Name 2 56 Name 3 33 Name 4 44 Name 5 11 Name 6 88 Put this formula on your sheet and drag down as far as you want. Change the ranges as required. =INDEX(A$1:A$100,MATCH(LARGE(B$1:B$100,ROW(A1)),B$ 1:B$100,0)) Mike "louiscourtney" wrote: Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 Ranking
Thanks everyone
Now i have the answers in a seperate tab i realised that i have one peice of information missing I now have the top ten in order by name and score what i need to add is the corresponding year which is in Column B3 down to B220 can this be done please "Bob Phillips" wrote: Assuming that the names are in A1:A26, score in B1:B26 on sheet 1, add this to C1 and copy down =RANK(B1,$B$1:$B$26)+COUNTIF($B$1:$B1,B1)-1 Select A1:A10 on sheet 2 and eneter this in the FORMULA BAR =INDEX(Sheet1!$A$1:$A$26,MATCH(ROW(INDIRECT("1:10" )),Sheet1!$C$1:$C$26,0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "louiscourtney" wrote in message ... Sorry to be rude but how do i do that? "bj" wrote: the rank() or large() function would identify the top ten people then on your new sheet an index match woul dpull the top ten over. "louiscourtney" wrote: I would like to be able to set up an extra tab in my worksheet that gives me the top 10 performers I can't use the normal filter sort as not all the cells are the same size and it all goes out of sync The sheet where the information is held is layed out as follows Column A has all the names from A3 down to A220 then there is various other columns with other data until we get to column P which has totals in it Column P3 down to P220 What i would like is some sort of caluculation that picks up the name then the corresphonding amount and then sorts it into the top 10 perfromers And then enters the information on the seperate tab Any help would be appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking | Excel Worksheet Functions | |||
Ranking | Excel Worksheet Functions | |||
Ranking | Excel Worksheet Functions | |||
Ranking | Excel Discussion (Misc queries) | |||
ranking | Excel Worksheet Functions |