Apple Numbers Multiple Filters?

Hi Bruce,

Thanks for the list. A little different from expected (tabs separating 'columns', return starting a new row, but not all that difficult to transfer the single column paste result into rows of seven values.

The columns showing TRUE and FALSE are the checkbox cells, and show TRUE if they were checked an False if they were unchecked. The others contain the data that was in them when copied.

Rows 1 and 2 are header rows. The two green filled cells in row 1 contain the text "List: " (F1(and a three item (All, Grocery, Packing) popup menu with which the filter is set.

The table has one new column (H, named 'Filter', containing a three tier nested IF formula that tests each line against the filter conditions for the filter chosen in G, and return a "Yes" if the row is to be shown and a "No" if it is not. Column H is intended to be hidden.

Here is a screen shot, showing the header rows and several rows that will be marked for hiding when a filter other than All is chosen.

Filter set to All: Formula in column H is shown below each view of the table.

With the filter set to All, the first IF test returns TRUE, and the formula immediately places "Yes" H40, the cell containing this copy of the formula.

Note that the first test has two parts: the filter is set to ALL, OR, the cell on this row of column G contains "Show". The second part of the OR ensures that the 'category rows' (such as row 40) will show in every filtered version of the table.

Same Table section with the filter set to Grocery.

Row 40 still gets a Yes, due to "Show" in G40. Other category header rows also get a Yes, and they all have "Show" in column G.

For rows without Show in column G, the second test is called.

This is a three part test. Part 1 requires that the filter is set to Grocery, part 2 that the checkbox in column G is set to TRUE (checked), and part three returns TRUE is the value in column B is greater than zero. If all three tests result TRUE, a yes is returned. Otherwise the next IF is called, the first part returns FALSE, and a No is returned to the cell containing the formula.

Same section of the table, with the filter set to Packing:

With the filter set to Packing the first two IFs return False (except on category header rows) and the third IF is called.

his checks that the filter is set to Packing, then tests each row for a quantity greater than zero in column B. f found, the test returns TRUE, the formula returns Yes. If not found, the test returns False, and the formula returns No.

The actual filter rule is set to show only rows with Yes in column H, as shown beside the table below, with column H hidden, and the filter activated:

The formula, as entered in cell H3, can be copied from here and pasted to H3, then filled down the whole column.

H3: IF(
OR(G$1="All",G3="Show"),"Yes",
IF(AND(G$1="Grocery",G3,B3>0),"Yes",
IF(AND(G$1="Packing",B3>0),"Yes",
"No")))

Regards,

Barry

HI Bruce,

The two filter rules you have set are sufficient to switch between the 'full view' shown in the image above the rule and the 'grocery list' image showing only the rows where QTY contains a value that is not equal to zero AND the Grocery checkbox is checked.

The missing "Produce" row is a mystery, as is the filter rule's labeling of the two conditions ranges "Bread" and "Show"

Is row 3 a Header row? That would explain "Bread" in the rule.and might also indicate "Show" is a default name to use when the cell from which that label would be copied is empty, as it is in your table.

So what you have (almost) is a pair of rules the filter the table 'this way' (as a grocery list). What other filters do you need?

With your reply, please include a copy of the DATA in the unfiltered table.

Click on cell A1 of the table to select it, then shift-click on the bottom right cell to expand the selection to 'all cells' on the table.

Copy.

Click in the compose box for your reply.

Paste.*

This pastes the data as a tab delimited list, which can be copied at this end and pasted into a new table in Numbers much more quickly (and accurately) than attemting to transcribe it from a screenshot image of the table.

Regards,

Barry

*When pasting the data, use command-V only if there are no formulas in the table. If there are formulas, go to the Edit menu and choose Paste Formula Results.

B.

I suggest ordering your table more like this:

then you can sort as needed by any column and filter by columns as well. In addition, you can use the categories feature:

make sure the first row is a header row in the table you want to sort.

Then use the Organize tool and select the "Sort" tab:

Now Choose the column to "Sort By"

No change the select in the "Sort By" menu:

"Is there a way…"

HI Bruce,

Yes, there is, but the way is specific to the details of your table and the details of 'this way' and 'that way' and 'the other way', etc.

Please provide the details requested by Yellowbox, including enough of the table in the screenshot to display the information/data by which the table is to be filtered.

Regards,

Barry

Pretty amazing you weren't able to find any helpful ideas in all the thoughtful posts above addressing your question, but it's good you found something that works for you.

SG

Hi Bruce,

Bruce Kieffer wrote:

Is there a way to set up multiple filters for one table? In other words, I want to see my table filtered "this way," then I want to see the same table filtered "that way."

Yes, there is, but we can't see "this way" and "that way".

To help us help you, please click on your table to select it so that it shows the column labels (A, B, C etc) and row labels (1, 2, 3 etc). Then post a screen shot of a small part of your table. (Full screen grabs are often difficult to read 🤨.)

And please explain your overall aim.

Regards,

Ian.