filter data by top 10 AND bottom 10 items simultaneously
 

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

filter data by top 10 AND bottom 10 items simultaneously
 

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(INDI RECT("1:10"))),0))))

and then filter by that column for TRUE

--
---
HTH

Bob


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



"Dingo" wrote in message
...
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

filter data by top 10 AND bottom 10 items simultaneously
 

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

"Bob Phillips" wrote:

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(INDI RECT("1:10"))),0))))

and then filter by that column for TRUE

--
---
HTH

Bob


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



"Dingo" wrote in message
...
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

filter data by top 10 AND bottom 10 items simultaneously
 

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.



--
---
HTH

Bob


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



"Dingo" wrote in message
...
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

"Bob Phillips" wrote:

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(INDI RECT("1:10"))),0))))

and then filter by that column for TRUE

--
---
HTH

Bob


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



"Dingo" wrote in message
...
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

filter data by top 10 AND bottom 10 items simultaneously
 

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

"Bob Phillips" wrote:

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.



--
---
HTH

Bob


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



"Dingo" wrote in message
...
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

"Bob Phillips" wrote:

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(INDI RECT("1:10"))),0))))

and then filter by that column for TRUE

--
---
HTH

Bob


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



"Dingo" wrote in message
...
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