Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5
Hi all,
I have Sheet1 which contains large amounts of data such as A B Fred £10 Joe £10 Tom £20 Sarah £30 etc Total £XYZ on sheet2 i would like to list the top 5 people (colA) based on their performance in (colB). I cannot change sheet1 in anyway, the only way i can find to do it, is to copy the entire thing to sheet to, do a rank() formula and then vlookup positions 2, 3, 4, 5, 6 (i don't use 1 as there is a total at the bottom). This gets a bit upset when there are blanks and when two or more people have the same value There must be a better way of doing this, so it can take into account when people have the same figure, ignoring the total at the bottom and without copying the entire thing! The number of rows of data is changeable and so i can't use i.e. A1:B50 when the totals are in B51, i need it either ignore the highest value or look at positions 2 to 6 whichever is easier for the formula Any help would be gratefully received |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5
Select you columns then:
Data==Filter==Autofilter==[Col B] (Top10 ..) option and change to 5 HTH "Emma Hope" wrote: Hi all, I have Sheet1 which contains large amounts of data such as A B Fred £10 Joe £10 Tom £20 Sarah £30 etc Total £XYZ on sheet2 i would like to list the top 5 people (colA) based on their performance in (colB). I cannot change sheet1 in anyway, the only way i can find to do it, is to copy the entire thing to sheet to, do a rank() formula and then vlookup positions 2, 3, 4, 5, 6 (i don't use 1 as there is a total at the bottom). This gets a bit upset when there are blanks and when two or more people have the same value There must be a better way of doing this, so it can take into account when people have the same figure, ignoring the total at the bottom and without copying the entire thing! The number of rows of data is changeable and so i can't use i.e. A1:B50 when the totals are in B51, i need it either ignore the highest value or look at positions 2 to 6 whichever is easier for the formula Any help would be gratefully received |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5
I can't change the sheets and i don't want to copy the whole sheet to another
(i have lots of sheets). Filters are not a possibility, please read the question before answering. Thanks! "Toppers" wrote: Select you columns then: Data==Filter==Autofilter==[Col B] (Top10 ..) option and change to 5 HTH "Emma Hope" wrote: Hi all, I have Sheet1 which contains large amounts of data such as A B Fred £10 Joe £10 Tom £20 Sarah £30 etc Total £XYZ on sheet2 i would like to list the top 5 people (colA) based on their performance in (colB). I cannot change sheet1 in anyway, the only way i can find to do it, is to copy the entire thing to sheet to, do a rank() formula and then vlookup positions 2, 3, 4, 5, 6 (i don't use 1 as there is a total at the bottom). This gets a bit upset when there are blanks and when two or more people have the same value There must be a better way of doing this, so it can take into account when people have the same figure, ignoring the total at the bottom and without copying the entire thing! The number of rows of data is changeable and so i can't use i.e. A1:B50 when the totals are in B51, i need it either ignore the highest value or look at positions 2 to 6 whichever is easier for the formula Any help would be gratefully received |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|