Find top 10 larget value from the list

[chiragsk]

Hi Friends,

New to Mr. Excel Forum. searched many blog but not reached to the solution. please help.

Query;
[TABLE="width: 576"]
<tbody>[TR]
[TD="colspan: 2"]Sheet: DATA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sheet: Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]month[/TD]
[TD]emp
code[/TD]
[TD]collection[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Rank[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]jan[/TD]
[TD]a1[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]a1[/TD]
[TD]a2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]jan[/TD]
[TD]a3[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]a5[/TD]
[TD]a3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]jan[/TD]
[TD]a5[/TD]
[TD]1200[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]a3[/TD]
[TD]a1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]jan[/TD]
[TD]a1[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]jan[/TD]
[TD]a3[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]jan[/TD]
[TD]a1[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]feb[/TD]
[TD]a2[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]feb[/TD]
[TD]a3[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]feb[/TD]
[TD]a1[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]feb[/TD]
[TD]a2[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In sheet RESULT, need to enter a formula that find the nth RANK from Sheet DATA in respect of Collection amount and give the emp code monthwise.

Thanks.

 

[Noodleski]

A pivot table has built in functionality to display a top ten/five/twenty whatever you like.
Have you tried defining your data as a table and then using it as a base for your pivot?

 

[chiragsk]

Thanks Noodleski.

We did it, but need formula based solution.