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

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

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

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

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

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

Excel 2007 PivotTable
Cloud file checks out OK,
but here is an alternate site:
http://www.mediafire.com/file/n1uhegtmlzw/04_27_10.xlsx