If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Identifying the top five values in multiple groups
I've got a spreadsheet of pay information for about eight hundred people.
Each person is on one of eight salary scales I'd like to create a new worksheet that shows the details of just the 5 highest paid people in each scale (name, dept, salary, etc.) - and also the five lowest. Ideally, I'd like also to be able to vary that number - eg the top ten, the highest, etc.. Can someone help? Thanks |
#2
|
|||
|
|||
Identifying the top five values in multiple groups
Suppose you have data in Sheet1 in the below format
Col A Col B Col C Name Scale Salary a1 1 101 a2 1 102 a3 1 103 a4 2 104 In Sheet2 cell A1 enter the scale In Sheet2 cell B1 enter the number of items to be displayed In cell A2 apply the below array formula and copy down to 10 cells =IF($B$1=ROW(A1),LARGE(IF(Sheet1!$B$1:$B$100=$A$1 , Sheet1!$C$1:$C$100),ROW(A1)),"") Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=formula}" In cell B2 apply the below formula and copy/drag to 10 cells =IF(A2"",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!C:C,0) ),"") For other columns change the column in the second formula (INDEX() ) to suit. -- Jacob (MVP - Excel) "RobFJ" wrote: I've got a spreadsheet of pay information for about eight hundred people. Each person is on one of eight salary scales I'd like to create a new worksheet that shows the details of just the 5 highest paid people in each scale (name, dept, salary, etc.) - and also the five lowest. Ideally, I'd like also to be able to vary that number - eg the top ten, the highest, etc.. Can someone help? Thanks |
#3
|
|||
|
|||
Identifying the top five values in multiple groups
Thanks Jacob,
I've tried the suggested formulae and they work as long as everything is unique values. If there are two values the same, then the index formula seems to repeat the first value it comes across. Is there a way around that? Regards, "Jacob Skaria" wrote in message ... Suppose you have data in Sheet1 in the below format Col A Col B Col C Name Scale Salary a1 1 101 a2 1 102 a3 1 103 a4 2 104 In Sheet2 cell A1 enter the scale In Sheet2 cell B1 enter the number of items to be displayed In cell A2 apply the below array formula and copy down to 10 cells =IF($B$1=ROW(A1),LARGE(IF(Sheet1!$B$1:$B$100=$A$1 , Sheet1!$C$1:$C$100),ROW(A1)),"") Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=formula}" In cell B2 apply the below formula and copy/drag to 10 cells =IF(A2"",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!C:C,0) ),"") For other columns change the column in the second formula (INDEX() ) to suit. -- Jacob (MVP - Excel) "RobFJ" wrote: I've got a spreadsheet of pay information for about eight hundred people. Each person is on one of eight salary scales I'd like to create a new worksheet that shows the details of just the 5 highest paid people in each scale (name, dept, salary, etc.) - and also the five lowest. Ideally, I'd like also to be able to vary that number - eg the top ten, the highest, etc.. Can someone help? Thanks |
#4
|
|||
|
|||
Identifying the top five values in multiple groups
Excel 2007 PivotTable
Top, bottom 5 All ties will show. No code, no formulas. Animated. http://c0718892.cdn.cloudfiles.racks.../04_27_10.xlsx Pdf preview: http://www.mediafire.com/file/w3jmmgm0jmj/04_27_10.pdf |
#5
|
|||
|
|||
Identifying the top five values in multiple groups
RobFJ wrote:
I've got a spreadsheet of pay information for about eight hundred people. Each person is on one of eight salary scales I'd like to create a new worksheet that shows the details of just the 5 highest paid people in each scale (name, dept, salary, etc.) - and also the five lowest. Ideally, I'd like also to be able to vary that number - eg the top ten, the highest, etc.. Can someone help? Thanks Try a PivotTable. If you aren't familiar with them, look he http://peltiertech.com/Excel/Pivots/pivotstart.htm |
#6
|
|||
|
|||
Identifying the top five values in multiple groups
Herbert,
Thanks for the response. However, I couldn't get anything to work in the following download - which took me to a zipped file : http://c0718892.cdn.cloudfiles.racks.../04_27_10.xlsx The best I can get is if I click onto something, IE opens and code is shown Is there something missing or something I should be doing (other than extracting them and looking for something relevant to open or run.) Thanks Rob |
#7
|
|||
|
|||
Identifying the top five values in multiple groups
Excel 2007 PivotTable
Cloud file checks out OK, but here is an alternate site: http://www.mediafire.com/file/n1uhegtmlzw/04_27_10.xlsx |
Thread Tools | |
Display Modes | |
|
|