Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why don't my filter results always appear on the bottom left? | Excel Discussion (Misc queries) | |||
In Excel how can I filter multiple columns SIMULTANEOUSLY? | Excel Discussion (Misc queries) | |||
One worksheet to calulate different items simultaneously | Excel Worksheet Functions | |||
How to have a the liist items appear at the top, not bottom | Excel Worksheet Functions | |||
No filter results in bottom left hand corner | Excel Discussion (Misc queries) |