How to find the bottom data

[gimran]

hello everyone,

I am a bit confused regarding the a issue stated below.

I have an changing data set with values in descending order. Now I want to find out the 3 or 4 smallest data in the data set. But i am afraid i could not figure it out how to do.

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

SID	NAME	Sales
9182	Sample1	23009
9092	Sample2	23008
3349	Sample3	23007
3345	Sample4	23006
2234	Sample5	23005

<tbody>
</tbody>

Now I want get the bottom the sales persons name and all the information across rows in a different table.

Please help me to know how I can do that.

Br
Imran

 

[gimran]

You are welcome.



What would be result, given the sample above?

The result would be the top 10 unique values in descending order. The output for this example would be:

Sales
3321
2356
2346
2345
2343
2234
1234

<tbody>
</tbody>

 

[Aladin Akyurek]

The result would be the top 10 unique values in descending order. The output for this example would be:

Sales
3321
2356
2346
2345
2343
2234
1234

<TBODY>
</TBODY>

Sales		10
2345		7
2346		Distinct
2356		3321
2345		2356
2343		2346
2234		2345
2346		2343
2356		2234
3321		1234
3321
2343
2356
1234

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><TBODY>
</TBODY>

C1: 10

C2, just enter:

=MIN(C1,SUM(IF(FREQUENCY(A2:A14,A2:A14),1)))

C4, control+shift+enter, not just enter, and copy down:

=IF(ROWS(C$4:C4)<=$C$2,MAX(IF(1-ISNUMBER(MATCH($A$2:$A$14,C$3:C3,0)),$A$2:$A$14)),"")

 

[gimran]

thanks a lot for the formula.............is there any other way without using an array formula?

 

[Aladin Akyurek]

thanks a lot for the formula.............is there any other way without using an array formula?

Sales		10
2345	2345	7
2346	2346	Distinct
2356	2356	3321
2345		2356
2343	2343	2346
2234	2234	2345
2346		2343
2356		2234
3321	3321	1234
3321
2343
2356
1234	1234

<tbody>
</tbody>

B2, just enter and copy down:

=IF(ISNUMBER(MATCH($A2,$A$1:A1,0)),"",$A2)

C1: 10

C2, just enter:

=MIN(C1,SUM(IF(FREQUENCY(A2:A14,A2:A14),1)))

C4, just enter and copy down:

=IF(ROWS(C$4:C4)<=C$2,LARGE($B$2:$B$14,ROWS(C$4:C4)),"")