Use filter to find any cells containing values from column

Question

Level 1

4 points

Use filter to find any cells containing values from column

I've mostly worked with Excel before but I'm having to achieve what I'm trying to do with Apple's Numbers application.

I have a spreadsheet full of values in column A on a table called Tracker.

I would like a separate table, Filter Criteria, which contains 1 column where I can paste a bunch of values in multiple cells and then I can activate a filter which will show me all of the cells in the Tracker table that are listed in Filter criteria table.

I would love to be able to do this with the built in Filter feature located in the sidebar on the right of the Numbers UI, however it seems to only let you type in text rather than referencing a cells or column of cells. Because my filter criteria could be quite long, it would take a long time to set up individual filters for each criteria value.

It would be great if someone could point me in the direction on how to achieve this.

Just to be clear, the 'expected filtered result' shouldn't be a new table, it should be the first table but simply filtered to only show the matching results.

Posted on Oct 29, 2020 2:50 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,532 points

Oct 30, 2020 3:07 AM in response to rbsam

You can do something like this:

The formula in C2 of the added Show column, filled or copied down.

=IF(COUNTIF(Filter Criteria::A,A2)>0,TRUE,FALSE)

Then set a filter as shown in the panel at the right.

You can then hide the extra column. The table will automatically update its display as you change the criteria.

Just "turn off" the filter with the switch in the right panel when you want to work with the whole table.

SG

Reply

Answer 2

Badunit

Level 7

30,342 points

Oct 29, 2020 5:54 PM in response to rbsam

Insert a new column in your Tracker table. it will be Column C.
In all cells of Column C except the header column put the formula =IFERROR(MATCH(A,Filter criteria::A,0)>0,"")
Create a filter for column C to show rows only where text is TRUE
Hide column C so it is no longer visible.

That is the pretty way with no error triangles. Alternatively and more simply, use the formula =MATCH(A,Filter criteria::A,0) and create a filter for column C to show rows only where the number is greater than 0. The non-matching rows will all get error triangles but you won't see them with the column hidden so they don't really matter.

Reply

Answer 3

Badunit

Level 7

30,342 points

Oct 29, 2020 6:04 PM in response to Badunit

One other note:

If you need to add rows to your Tracker table, you MUST MUST MUST turn off the filter first. If you leave it on, the formula in column C will not automatically fill into the newly added rows, it will instead fill new rows with "the last calculated value". If the filter is off, newly added rows will get a copy of the column C formula.

Reply

Use filter to find any cells containing values from column

Similar questions