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

Use autofilter and select top 10


--


Regards,


Peo Sjoblom


"Joe Maldon" wrote in message
oups.com...
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

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

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

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 -

I see, I missed that he wanted to extend the list if there were duplicate
top values regardless if they were different people. That is kind of strange
really, isn't? Anyway by using a help column and autofilter like this


=SUMPRODUCT(--($C$2:$C$41<""),--($C$2:$C$41C2),1/COUNTIF($C$2:$C$41,$C$2:$C$41&""))+1

in D2 copied down

then use custom filter on the help column criteria custom less than or equal
to 10

will give this list


ID Names Total Rank
20 Name20 1000 1
26 Name26 996 2
7 Name7 965 3
37 Name37 964 4
31 Name31 958 5
27 Name27 958 5
15 Name15 927 6
35 Name35 906 7
10 Name10 899 8
9 Name9 899 8
16 Name16 873 9
4 Name4 856 10



--


Regards,


Peo Sjoblom



"iliace" wrote in message
oups.com...
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 -

Oh that's so much better.

On Oct 24, 4:52 pm, "Peo Sjoblom" wrote:
I see, I missed that he wanted to extend the list if there were duplicate
top values regardless if they were different people. That is kind of strange
really, isn't? Anyway by using a help column and autofilter like this

=SUMPRODUCT(--($C$2:$C$41<""),--($C$2:$C$41C2),1/COUNTIF($C$2:$C$41,$C$2:*$C$41&""))+1

in D2 copied down

then use custom filter on the help column criteria custom less than or equal
to 10

will give this list

ID Names Total Rank
20 Name20 1000 1
26 Name26 996 2
7 Name7 965 3
37 Name37 964 4
31 Name31 958 5
27 Name27 958 5
15 Name15 927 6
35 Name35 906 7
10 Name10 899 8
9 Name9 899 8
16 Name16 873 9
4 Name4 856 10

--

Regards,

Peo Sjoblom

"iliace" wrote in message

oups.com...



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

groups.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 -- Hide quoted text -

- Show quoted text -

Brilliant. Thanks for the help here guys. Works like a charm.

Can I thow another 'spanner into the works'? Sorry! ... How would I achieve
the result seen here http://joemaldon.googlepages.com/?

Many thanks.

Joe Maldon.


"Peo Sjoblom" wrote in message
...
I see, I missed that he wanted to extend the list if there were duplicate
top values regardless if they were different people. That is kind of
strange really, isn't? Anyway by using a help column and autofilter like
this


=SUMPRODUCT(--($C$2:$C$41<""),--($C$2:$C$41C2),1/COUNTIF($C$2:$C$41,$C$2:$C$41&""))+1

in D2 copied down

then use custom filter on the help column criteria custom less than or
equal to 10

will give this list


ID Names Total Rank
20 Name20 1000 1
26 Name26 996 2
7 Name7 965 3
37 Name37 964 4
31 Name31 958 5
27 Name27 958 5
15 Name15 927 6
35 Name35 906 7
10 Name10 899 8
9 Name9 899 8
16 Name16 873 9
4 Name4 856 10



--


Regards,


Peo Sjoblom



"iliace" wrote in message
oups.com...
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 -

Anyone help with this one please?

Many thanks.

Joe Maldon.


"Joe Maldon" wrote in message
...
Brilliant. Thanks for the help here guys. Works like a charm.

Can I thow another 'spanner into the works'? Sorry! ... How would I
achieve the result seen here http://joemaldon.googlepages.com/?

Many thanks.

Joe Maldon.


"Peo Sjoblom" wrote in message
...
I see, I missed that he wanted to extend the list if there were duplicate
top values regardless if they were different people. That is kind of
strange really, isn't? Anyway by using a help column and autofilter like
this


=SUMPRODUCT(--($C$2:$C$41<""),--($C$2:$C$41C2),1/COUNTIF($C$2:$C$41,$C$2:$C$41&""))+1

in D2 copied down

then use custom filter on the help column criteria custom less than or
equal to 10

will give this list


ID Names Total Rank
20 Name20 1000 1
26 Name26 996 2
7 Name7 965 3
37 Name37 964 4
31 Name31 958 5
27 Name27 958 5
15 Name15 927 6
35 Name35 906 7
10 Name10 899 8
9 Name9 899 8
16 Name16 873 9
4 Name4 856 10



--


Regards,


Peo Sjoblom



"iliace" wrote in message
oups.com...
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 -

Thanks

"Joe Maldon" wrote in message
...
Anyone help with this one please?

Many thanks.

Joe Maldon.


"Joe Maldon" wrote in message
...
Brilliant. Thanks for the help here guys. Works like a charm.

Can I thow another 'spanner into the works'? Sorry! ... How would I
achieve the result seen here http://joemaldon.googlepages.com/?

Many thanks.

Joe Maldon.


"Peo Sjoblom" wrote in message
...
I see, I missed that he wanted to extend the list if there were duplicate
top values regardless if they were different people. That is kind of
strange really, isn't? Anyway by using a help column and autofilter like
this


=SUMPRODUCT(--($C$2:$C$41<""),--($C$2:$C$41C2),1/COUNTIF($C$2:$C$41,$C$2:$C$41&""))+1

in D2 copied down

then use custom filter on the help column criteria custom less than or
equal to 10

will give this list


ID Names Total Rank
20 Name20 1000 1
26 Name26 996 2
7 Name7 965 3
37 Name37 964 4
31 Name31 958 5
27 Name27 958 5
15 Name15 927 6
35 Name35 906 7
10 Name10 899 8
9 Name9 899 8
16 Name16 873 9
4 Name4 856 10



--


Regards,


Peo Sjoblom



"iliace" wrote in message
oups.com...
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 -