I don't understand. He wrote:

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

Your list contains 10 items whereas it should be 12. I was trying to
come up with a unique rank formula but failed, so I went with the
around-the-bend-with-arrays approach for lack of a better idea, to
achieve the same result. What he really needs is a formula that will
return 3 for all occurences of 965 and 5 for all occurences of 958, in
your example. That way when you filter from 1 to 10 on the rank
column, you will get all the values you need.

Also, if you do try my solution (might want to set recalc to manual
just in case), I said that RankList needs to be extended far enough to
accomodate all data. This is not true; it only needs to be 10 cells
long. In the Rank column with the match formula, you will get #NA for
any numbers not in top 10, but calculation will be faster.





On Oct 24, 3:00 pm, "Peo Sjoblom" wrote:
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- Hide quoted text -

- Show quoted text -