mdj7
Level 1 (0 points)

Q: Filter limit or bug?

I'm trying to add filters to a spreadsheet. I want to add 12 filters to the spreadsheet. I've set "Show rows that match" to "any filter". I'm filtering on the "City" column (Data format is Automatic) and I've chosen the filtering rule "text is" with an "or" between each of the filters. I was able to add 7 cities to the filter list, but when I added the 8th city and hit the Return key, it would not activate the "or..." button. It remains greyed out and will not let me add the remaining 4 cities.

 

This is either a bug or a pretty major design flaw. Or, I'm doing something wrong?

MacBook Air (11-inch Mid 2012), OS X El Capitan (10.11.3), Numbers version 3.6.1.

Posted on Feb 10, 2016 5:16 AM

by macmikey,Apple recommended
macmikey macmikey
Level 1 (41 points)
Mac OS X
A:

The only solution I've been able to make work is thus:

  1. Create a "marker" column that contains a value (even a dot or something) after testing with an IF statement with lots of OR conditions (for instance, =IF(OR(City_cell="City1",City_cell="City2",City_cell="City3", etc...),"•","")
  2. Set a filter to eliminate any row containing a your value in the marker column

There may be a quicker or more elegant way; additionally, you'll have to have this formula in each row of your table. Fortunately, Numbers 3.x handles this kind of mass formula stuff better than previous versions. There are other things it does... less well, but the formula engine is considerably more robust. (That may be debatable, but that's been my experience.)

Posted on Feb 11, 2016 11:41 AM

Close
mdj7

Q: Filter limit or bug?

  • All replies
  • Helpful answers

  • by Barry,

    Barry Barry Feb 10, 2016 11:18 AM in response to mdj7
    Level 7 (32,271 points)
    Feb 10, 2016 11:18 AM in response to mdj7

    Of historical interest, but possibly not applicable to Numbers 3: Numbers '09 also has an apparent maximum of 8 filtering rules. That would suggest the behaviour is not a bug.

     

    Possible workaround: If your cities are in one or more relatively 'fixed' sets, it should be possible to generate a code for each of the cities that would include a letter pair (or trio or longer) for each of the sets to which it belongs. Filtering for "contains" (the set code) would show all the cities that belong in that set.

     

    I've not investigated this further, and likely won't have time to do so in the next few days, but it may provide a starting point for someone.

     

    Regards,

    Barry

  • by macmikey,Apple recommended

    macmikey macmikey Feb 11, 2016 11:41 AM in response to mdj7
    Level 1 (41 points)
    Mac OS X
    Feb 11, 2016 11:41 AM in response to mdj7

    The only solution I've been able to make work is thus:

    1. Create a "marker" column that contains a value (even a dot or something) after testing with an IF statement with lots of OR conditions (for instance, =IF(OR(City_cell="City1",City_cell="City2",City_cell="City3", etc...),"•","")
    2. Set a filter to eliminate any row containing a your value in the marker column

    There may be a quicker or more elegant way; additionally, you'll have to have this formula in each row of your table. Fortunately, Numbers 3.x handles this kind of mass formula stuff better than previous versions. There are other things it does... less well, but the formula engine is considerably more robust. (That may be debatable, but that's been my experience.)

  • by Wayne Contello,Apple recommended

    Wayne Contello Wayne Contello Feb 11, 2016 12:27 PM in response to macmikey
    Level 6 (19,037 points)
    iWork
    Feb 11, 2016 12:27 PM in response to macmikey

    You could do something like this to see if a city is in a list:

     

     

    Make a table with a list of cities (named "City"):

    City

    Include

    Austin

    TRUE

    Houston

    TRUE

    Dallas

    FALSE

    San Marcos

    TRUE

    Fort Worth

    TRUE

    Atlanta

    FALSE

    Mobile

    FALSE

    Tampa

    FALSE

    Round Rock

    FALSE

    Cedar Park

    FALSE

    The include column is actually checkboxes which you can check or uncheck as needed.  When you paste a table from Numbers to this forum it gets changed to the boolean values "TRUE" or "FALSE"

     

     

    then

    In another table:

    A     B          C

     

    City

    In List

     

    Anchorage

    FALSE

     

    Atlanta

    FALSE

     

    Austin

    TRUE

     

    Boston

    FALSE

     

    Cedar Park

    FALSE

     

    Chicago

    FALSE

     

    Dallas

    FALSE

     

    Denver

    FALSE

     

    Fort Worth

    TRUE

     

    Houston

    TRUE

     

    Las Vegas

    FALSE

     

    Litle Rock

    FALSE

     

    Mobile

    FALSE

     

    Round Rock

    FALSE

     

    San Marcos

    TRUE

     

    Santa Fe

    FALSE

     

    Tampa

    FALSE

     

    Tuscon

    FALSE

     

    select cell C2, then type (or copy and paste from here) the formula:

    =IF(B2<>"",IF(COUNTIFS(City::A, B2, City::B, TRUE)>0, TRUE, FALSE), "")

     

    shorthand for this is:

    C2=IF(B2<>"",IF(COUNTIFS(City::A, B2, City::B, TRUE)>0, TRUE, FALSE), "")

     

    select cell C2, copy

    select cells C2 thru the end of column c, paste

  • by macmikey,

    macmikey macmikey Feb 10, 2016 4:31 PM in response to Wayne Contello
    Level 1 (41 points)
    Mac OS X
    Feb 10, 2016 4:31 PM in response to Wayne Contello

    I had to stare at this for a while before I got what you were doing. Yes, your solution is much better than mine--using a lookup table that can dynamically change if more cities are ever needed. Mine requires manually changing ALL the IFs.

  • by SGIII,

    SGIII SGIII Feb 10, 2016 7:24 PM in response to macmikey
    Level 6 (10,647 points)
    Mac OS X
    Feb 10, 2016 7:24 PM in response to macmikey

    Nice answer.  Keep posting!

     

    SG

  • by t quinn,Apple recommended

    t quinn t quinn Feb 12, 2016 7:51 AM in response to macmikey
    Level 5 (5,012 points)
    Mac OS X
    Feb 12, 2016 7:51 AM in response to macmikey

    Hi all,

     

    I am a big fan of leveraging a filter column to increase the power of filters. Here is a variation on Wayne's approach that does it all in one table. The check box in A1 controls whether the filter is applied or not and could easily be in the same row as "City" and "filtered" and so eliminate my first row. I wanted to label it here "filter on".

    Screen Shot 2016-02-12 at 8.31.19 AM.png

     

    Screen Shot 2016-02-12 at 8.48.58 AM.png

    WIth the filter on checking a city will make it disappear.

    Screen Shot 2016-02-12 at 8.47.24 AM.png

    And we can hide column C.

     

    quinn

  • by SGIII,

    SGIII SGIII Feb 12, 2016 4:44 PM in response to t quinn
    Level 6 (10,647 points)
    Mac OS X
    Feb 12, 2016 4:44 PM in response to t quinn

    Nice, quinn.  Should work well in iOS too.

     

    SG

  • by t quinn,

    t quinn t quinn Feb 12, 2016 7:20 PM in response to SGIII
    Level 5 (5,012 points)
    Mac OS X
    Feb 12, 2016 7:20 PM in response to SGIII

    Thanks SG,

     

    I have used an expanded formula to save different filters and switch easily between them in iOS. The strings of AND() and OR() can get hairy if you are creating multiple filters. Also Applescript.

     

    quinn