Numbers - filter multiple values in a single column

HI Jon,

"I think, match any filters is what should work, but match all is what gives the desired result...."

That depends on the construction of the filters.

(Show rows that) match ALL filters does what it says:

• Rows containing "Rock" are not shown because they do not match Filter 1.
• Rows containing "Synth Pop" are not shown because they do not match Filter 2.
• All other rows match Filter 1 because they do not contain "Rock" AND match Filter 2 because they do not contain "Synth Pop". They are shown because they match all filters.

Regards,

Barry

Hi Jon,

I think I've discovered where it's going wrong.

I am assuming the only difference between the two cases shown in your most recent post is the "Match Any Filters" "Match All Filters" setting (missing from the first image).

Either of these settings will produce the same result, because you have specified only ONE Filter.

If "Any" filter in a set of one is matched, then All filters in that set of one are matched

The ONE Filter in your examples consists of two rules, connected by "or" If either rule returns 'true', then the Filter condition is met.

Put each rule into a separate Filter, set the Match menu to Match All Filters, as shown below.

When the filter is activated (Checkbox to right of "Filters") this result is seen (note missing row numbers in the reference tabs left of the table):

Regards,

Barry

Regarding: "[T]here appears to be a limit of about 8 filters then the option to create another is greyed out "

Here's an alternate that could get you past the eight filter limit.

This version uses a single Filter, with a single rule: Show this row if the cell in column F contains "show"

The harder part i getting that cell to show "show"

To the right of the main table is a second table containing two columns and enough rows to handle as many genres as you have named. For the example, that is 15 genres, listed on if copies of a pop-up menu cell in column 1.

The first cell (A2 contains an extra menu item—All—making it easy to display all rows without having to set each menu to one of the genres.

In the image above, A2 is set to All, and the rest of the cells are set to "xxxxxxxxx" (nine x characters)

Here is the simple Filter rule used in this solution.

In the image below, three of the pop-up menu cells have been set to three different genres. Noe that it is not necessary that the hree set menus be in contiguous rows be three contiguous cells. Note also that the Text string cell contains a single string of the three genres that have been selected on three of the pop-up menu cells.

Since the filter is active, we see only rows containing one of the selected genres (and the text "show". in column E. (Column D has been hidden to take less space).)

The formula below the tables is entered in cell B2 of the second table. Its purpose is to create the Test string in that cell.

In this formula, CONCATENATE joins the string values displayed in the pop-up menus in cells A2 to A16 of this table into a single string will no spaces other than those in the genre names.

SUBSTITUTE then replaces each group of "xxx" with a null string, removing all the 'x" characters set by the pop-up menus set to xxxxxxxxx. he result is a string containing only the names of the genres selected to show.

In the image below, the formula shown near row 12 of the table is entered in cell E2 of the main table, and filled down to the last row containing an entry.

IF All is selected in the first pop-up menu in Table 2, the first conparison returns 'true', making OR return 'true', and IF places the text 'show' in this row of column E.

If All is not selected, OR evaluates the second comparison: LEN(Table 2::B$2)>LEN(SUBSTITUTE(Table 2::B$2,B2,"")

LEN measures the length of a string in characters. Substitute looks for a string of characters (in this case, the text in B2 or 'this table' in the test string, and if it finds that string, replaces it with a null string. This shortens the original string, making it's measurement by LEN shorter than the original. The comparison returns 'true', OR returns 'true', and IF places 'show' in the cell containing the formula.

Is the text in B2 of this table is not found in the test string, no substitution is made, the length of the string remains the same, IF receives 'false' from the comparison, no change in made in the LENgth of the test string, and IF places "hide" in the cell.

As column E is filled with "show" or "hide", the single filter immediately shows rows containing "show" and hides those that do not show "show".

REgards,

Barry

Hi Jon,

Another approach (standing on the shoulders of giants Badunit and Barry).

Formula in C2 of the List table =VLOOKUP(B2,Selector::A:B,2)

Formula in D2 of the List table =IF(Show All::A$1,"Show",IF(C2=TRUE,"Show",""))

Fill both formulas down.

Filter the List table by column D (Text is Show).

The Show All table is your panic button.

Regards,

Ian.

Hi Jon,

Filters determine which rows to Show, not which to hide.

With the control set to "match any filter" only one filter's condition needs to be met for the row to be shown.

Synth Pop and Synth are shown because they 'are not Rock' and match the first filter.

Rock is shown because it 'is not Synth' and matches the second filter.

The third filter is never called on because the only genre that won't match the first filter is Rock, and that genre will be approved by the second filter.

Switch that to "Match all filters" and you'll get the results you want.

Regards,

Barry

PS: Essentially the same asnwer as presented by Badunit's illustration.

Did the filter I posted work for you?

Your filter, as described in plain text, is "Show rows where genre is not rock OR genre is not synth pop".

Let's try an example row and see what should logically happen. Let's say a row has the genre "rock":

"is not rock" = FALSE

"is not synth pop" = TRUE

OR(FALSE,TRUE) = TRUE

Since the result is TRUE, the row will be shown.

The filter I provided is two filters that must both be met.

"is not rock" = FALSE

"is not synth pop" = TRUE

AND(FALSE, TRUE) = FALSE

Since the result is FALSE, the row will not show