Using visible results from filtered table to create a new table WITHOUT the hidden data (or filter settings)?

Question

Level 1

8 points

Using visible results from filtered table to create a new table WITHOUT the hidden data (or filter settings)?

I have a table with a couple thousand rows of financial transactions. Pretty simple table and data: 3 columns -- Date, Amount, Transaction. I've successfully applied filters to display just all deposits (ETF deposits, checks, payroll direct deposits, etc.), while hiding all other transactions. Now I want to create a new table of just the deposits that remain visible. But no matter how I try to select and copy the deposit rows (while the filters are still actively applied to hide all other transactions) into a new table -- just containing the rows of deposits -- all the underlying, hidden rows are still there. I've proven this in several ways, so just trust me that all of that "hidden" data is still in the "new" table I've tried to create. It seems like this would be a no-brainer. I really just want to extract all the "deposit" rows from the original table of "all transactions" and create a new table containing just the deposits. Any simple solutions?

iPad mini 4, iOS 12

Posted on Mar 14, 2019 10:20 AM

Reply

Answer 1

Badunit

Level 7

25,628 points

Mar 14, 2019 4:47 PM in response to dforer

This is not a feature of Numbers, unfortunately. There are a few ways that can accomplish it but I don't know if either will work for you.

First method

Copy the rows,
Paste it into TextEdit
Copy it from TextEdit
Paste into a blank spot on a Numbers Sheet or create a new table, select the top left cell, then Paste and Match Style.

All formulas will be gone, it will use the last calculated values. That's not a problem if it is just data. The formatting of the table may be different.

Second Method

Sort the table so the rows you want are all in one contiguous range.
Select the rows (there will be no hidden rows to be selected so no problem there)
Copy
Paste into a blank spot on the sheet

All formulas will be retained but if any refer to cells that were not copied from the original table, they will continue referring to the original table (as they should). Formatting will be retained.

I wish there was a more direct way, a "copy visible cells only" command, but I am not aware of one. Maybe others will have some additional ideas for you.

Reply

Answer 2

SGIII

Level 8

35,264 points

Mar 14, 2019 8:07 PM in response to dforer

I really just want to extract all the "deposit" rows from the original table
 of "all transactions" and create a new table containing just the deposits.
 Any simple solutions?

Yes, there's a very simple solution, hidden right in plain sight. It's built into Numbers.

Just start a new empty table first and paste into that. 1) Select the visible cells in the original, filtered table, 2) type command-c to copy to the clipboard, 3) click once in a cell in the new empty table, and 4) command-v or Edit > Paste and Match Style to paste. Done.

The trick is to paste into an existing table, not paste onto the canvas. If you paste onto the canvas the hidden rows will also be included, as you have discovered.

SG

Reply

Answer 3

SGIII

Level 8

35,264 points

Mar 16, 2019 7:20 AM in response to Yellowbox

Hi Ian,

So you were already working with spreadsheets just a few years after VisiCalc for Apple II?

I read with some astonishment that Excel was first released back then on the Mac, then ported to the PC. Can this be true?

SG

Reply

Answer 4

SGIII

Level 8

35,264 points

Mar 17, 2019 3:35 PM in response to stfflspl

But first there was the Apple II and then there was the Mac, right?

VisiCalc was on the Apple II and then maybe on CP/M before SuperCalc. But was it ever on the Mac?

It seems Excel may have been the first on the Mac.

SG

Reply

Answer 5

Badunit

Level 7

25,628 points

Mar 15, 2019 4:48 AM in response to SGIII

What the heck. I could swear I tried that;, first thing in fact. I feel stupid.

Reply

Answer 6

SGIII

Level 8

35,264 points

Mar 15, 2019 5:49 AM in response to Badunit

Some of the little "tricks" in the Numbers interface could be better documented. I discovered this "by accident" a few years ago, and have found it handy ever since.

SG

Reply