Prev: how do I double check that there are no formulas
Next: Date formula needed
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 "{=<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
>
>
>
>
>
>
>
>
>
>
>
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" <JacobSkaria(a)discussions.microsoft.com> 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 "{=<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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
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 formulas
Next: Date formula needed