iWork Numbers: how to copy only filtered values?

Question

I'm using Apple's iWork Numbers to look through some big spreadsheets of economic data. I used the Sort & Filter Panel to filter out just the rows of data I need, and now I'd like to copy just those rows into a new Numbers document. But I can't seem to figure out how to do that.

If I select the filtered rows and press ⌘-c it copies all of the data, including the stuff that's not visible due to the filter. I just want to copy the data that's visible, but I can't find a command to do that.

Does anybody know how?

Answer 1

The Numbers manual (look for in on Apple's website, I just have the pdf with me on my desktop) states: When you sort table cells, values in hidden rows are taken into account.

And you can see this when you filter: the row numbers that correspond to your filter/sort appear, ignoring the row numbers that don't pass your filter/sort.

There is a solution for this. After filtering, hold down the return key to select the cells that you want. After selecting, hold command-c. Then deselect the table and press command-v to paste. This will create a table with the rows you selected and the empty rows. You can then sort the rows and delete the empty rows.

Note: You have to be very precise about the cells you want. If you just drag across with the mouse you might accidently pick up the rows you don't care about.

Granted, this isn't the most elegant way, but from what I've seen of the documentation and my own experimentation, it's the only way...and it's a lot better than hunting through the spreadsheet yourself.

Answer 2

I do this using the following method:

1. Filter the original table so that the rows you want are left visible and those that you don't are hidden.
2. Select the whole table copy it (command + c).
3. Create a blank table.
4. Select the first cell (A1).
5. Paste (command + v)

This puts only the visible cells into the blank table, expanding columns and rows if necessary.

Answer 3

I found the most elegant (for myself) way to do this, hope this will help somebody:

1. Filter the original table
2. Select the first Row, hold ⇧ Shift, select the last Row
3. Copy (⌘+C)
4. Insert sheet (⇧+⌘+N) and delete empty table
5. Paste (⌘+V) | all filtered rows will be inserted as hidden and all filter criteria will be reset
6. Select any row → RMB (or ⌃+LMB) → Unhide All Rows | also highlights all the hidden
7. RMB (or ⌃+LMB) → Delete Selected Rows

Answer 4

The 'Filter' mechanism is described in the Apple documentation as a visibility feature, i.e, filtering seems to be designed as a way of viewing the data - rather than a way of organising the data.

If you want to organise the data (as the OP seemed to want to do) try using the 'Sort' mechanism based on the column(s) you wanted to filter.

Either (1) copy the whole table into a new one, sort this new table based on the required column(s) and then manually select the rows that don't meet the required criteria and delete them all or (2) sort the original table and manually select the rows that meet the criteria and copy/paste these into a new table. (ps, calculate some control/hash totals on the original table before manipulating it that you can check back to the amended table so that you know you've ended up with the right data.)