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.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]SID[/TD]
[TD]NAME[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]9182[/TD]
[TD]Sample1[/TD]
[TD]23009[/TD]
[/TR]
[TR]
[TD]9092[/TD]
[TD]Sample2[/TD]
[TD]23008[/TD]
[/TR]
[TR]
[TD]3349[/TD]
[TD]Sample3[/TD]
[TD]23007[/TD]
[/TR]
[TR]
[TD]3345[/TD]
[TD]Sample4[/TD]
[TD]23006[/TD]
[/TR]
[TR]
[TD]2234[/TD]
[TD]Sample5[/TD]
[TD]23005[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]3321[/TD]
[/TR]
[TR]
[TD]2356[/TD]
[/TR]
[TR]
[TD]2346[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[/TR]
[TR]
[TD]2343[/TD]
[/TR]
[TR]
[TD]2234[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

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

[TABLE="class: grid, width: 50, align: left"]
<TBODY>[TR]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]3321
[/TD]
[/TR]
[TR]
[TD]2356
[/TD]
[/TR]
[TR]
[TD]2346
[/TD]
[/TR]
[TR]
[TD]2345
[/TD]
[/TR]
[TR]
[TD]2343
[/TD]
[/TR]
[TR]
[TD]2234
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 168"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Sales[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 96, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2345[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2346[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]Distinct [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2356[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3321[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2345[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2356[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2343[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2346[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2234[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2345[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2346[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2343[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2356[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2234[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]3321[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1234[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]3321[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2343[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]2356[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]1234[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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?

[TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Sales
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]10
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2345
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]2345
[/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]7
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2346
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]2346
[/TD]
[TD="class: xl67, width: 188, bgcolor: transparent"]Distinct
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2356
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]2356
[/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]3321
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2345
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]2356
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2343
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]2343
[/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]2346
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2234
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]2234
[/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]2345
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2346
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]2343
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2356
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]2234
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]3321
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]3321
[/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"]1234
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]3321
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2343
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]2356
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]1234
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]1234
[/TD]
[TD="class: xl65, width: 188, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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)),"")