From: RobFJ on 27 Apr 2010 08:35 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 From: Jacob Skaria on 27 Apr 2010 10:23 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 "{=}" 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 > > > > > > > > > > > From: RobFJ on 27 Apr 2010 11:44 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 news:1FD05646-BA25-47CE-B5B6-6255448711BF(a)microsoft.com...> 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 "{=}" > > > 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 >> >> >> >> >> >> >> >> >> >> >> > From: Herbert Seidenberg on 27 Apr 2010 12:11 Excel 2007 PivotTable Top, bottom 5 All ties will show. No code, no formulas. Animated. http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_27_10.xlsx Pdf preview: http://www.mediafire.com/file/w3jmmgm0jmj/04_27_10.pdf From: Glenn on 27 Apr 2010 12:16 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 here: http://peltiertech.com/Excel/Pivots/pivotstart.htm  |  Next  |  Last Pages: 1 2 Prev: how do I double check that there are no formulasNext: Date formula needed