filter data by top 10 AND bottom 10 items simultaneously

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dingo - 11 Feb 2008 07:36 GMT

How can I filter data to show the top 10 AND the bottom 10 items at the same
time? The Autofilter gives the option of one or the other but not both. My
data is set out as shown below. I've used a macro to filter using advanced
criteria for 'Sector' but I would like to be able to filter just by top x and
bottom x number of items.

Row 1: Value Sector
Small Cap

Row 5: Value Sector
6: -41 Industrial
7: -36 Small Cap
8: -34 Technology
9: 33 Industrial

Any help is much appreciated. Thanks

Reply to this Message

Bob Phillips - 11 Feb 2008 09:21 GMT

Add this array formula in a helper column, based upon the the numbers in
column A

=OR(ISNUMBER(N(MATCH(A2,SMALL(A:A,ROW(INDIRECT("1:10"))),0))),ISNUMBER(N(MATCH(A2,LARGE(A:A,ROW(INDIRECT("1:10"))),0))))

and then filter by that column for TRUE

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> How can I filter data to show the top 10 AND the bottom 10 items at the
> same
[quoted text clipped - 14 lines]
>
> Any help is much appreciated. Thanks

Reply to this Message

Dingo - 11 Feb 2008 10:11 GMT

Hi Bob, thanks for advice but I can't get it to work - all the formulas
return "FALSE". In the example below should I write this formula in cell C6
(if "Value" and "Sector" fields are in columns A & B). Should the A2 in your
formula refer to the empty cell A2 in my example or is that the first cell
with values in (A6 in my example)? Should the A:A refer to the entire column
or should i narrow the range to incl just the data eg. A6 to A200?
Thanks, Dingo

> Add this array formula in a helper column, based upon the the numbers in
> column A
[quoted text clipped - 21 lines]
> >
> > Any help is much appreciated. Thanks

Reply to this Message

Bob Phillips - 11 Feb 2008 12:36 GMT

Yes, put the formula in a column in row 6 and change A2 to A6.

I did mention that it is an array formula. perhaps that needs clarification
for you.

... which is an array formula, it should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob, thanks for advice but I can't get it to work - all the formulas
> return "FALSE". In the example below should I write this formula in cell
[quoted text clipped - 35 lines]
>> >
>> > Any help is much appreciated. Thanks

Reply to this Message

Dingo - 11 Feb 2008 16:45 GMT

Thanks very much, Bob!! Got it to work.

> Yes, put the formula in a column in row 6 and change A2 to A6.
>
[quoted text clipped - 46 lines]
> >> >
> >> > Any help is much appreciated. Thanks

Reply to this Message