Actually it will work given the OP's table, his names and IDs are unique
and if filtered top 10 on the total and then sorted in the filter descending
will give this table

ID Names Total
31 Name31 1000
10 Name10 996
26 Name26 965
37 Name37 965
15 Name15 964
7 Name7 958
27 Name27 958
20 Name20 927
35 Name35 906
9 Name9 899



I haven't looked at your formulas deeply but they look awfully complicated
for this task
One can use a combination of countif and index to extract a sorted list and
then another formula
to get the distinct values without having to involve volatile functions
like INDIRECT
But given the OP's table layout it can be done using autofilter





--


Regards,


Peo Sjoblom





"iliace" wrote in message
oups.com...
AutoFilter will not work because it will show top 10 items - if you
have one item that is top 1 and repeats 10 times, that's all you'll
get.

This might be a bit of a roundabout way of doing this, but it works.

First, you need to define two names. First one I called SortedList.
It will use your total column and sort the values in descending
order. Replace the Sheet1!$B$2:$B$52 in my example with your total
range, not including the header. Type it in as follows:

=LARGE(Sheet1!$B$2:$B$52,ROW(INDIRECT("1:"&ROWS(Sh eet1!$B$2:$B$52))))

Second name is defined like this, and call it SortedUniques:

=INDEX(SortedList,SMALL(IF(MATCH(SortedList,Sorted List,
0)=ROW(INDIRECT("1:"&ROWS(SortedList))),MATCH(Sort edList,SortedList,
0),""),ROW(INDIRECT("1:"&ROWS(SortedList)))))

Finally, you'll need a place (possibly another worksheet) where you
array-enter the SortedUniques. Use this formula:

=IF(ISERROR(SortedUniques),"",SortedUniques)

Use a multi-cell array and confirm with Ctrl+Shift+Enter; be sure the
array is large enough to fill all values. Name this RankList, to use
in the next step.

Insert a helper column in your MainList and call it Rank or
something. Use this formula:

=MATCH(B2,RankList,0)

Assuming the total starts in B2, and copy down. Finally, use
AutoFilter and select values 1 through 10.

If anyone can think of an easier way, I'd love to see it!


On Oct 24, 12:06 pm, Joe Maldon wrote:
Hi,

I'm looking for a way to search a range in a list and pick the top 10
from that list. Once this has been done, I then need to copy the data
to another sheet elsewhere

For example, I have a sheet called "MainList" (http://
joemaldon.googlepages.com/), I would like to pick the top 10 from the
totals colum and then take each entry and copy into another sheet
called "Top10". So, copy ID, Name and Total to sheet called "Top10".
Also then add rank positions including the joint places.

Can anyone please offer a solution how this can be done please?

Note there are two entries with the same total so the top 10 would
actually consist of twelve entries. Hope that makes sense.

Many thanks.

Joe Maldon