Filter limit or bug?

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

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.)

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"):

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

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

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.

Nice answer. Keep posting!

SG

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".

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

And we can hide column C.

quinn

Nice, quinn. Should work well in iOS too.

SG

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