Apple Numbers (MacOS) - how to automatically copy rows to another table?
June 8, 2024 12:49 PM Subscribe
In Apple Numbers I have a table that I add data to frequently. How do I get all rows in this table that contain a certain value in column X (actually a word from a drop-down menu) to be automatically copied in their entirety to a second table? I know they could just be copy/pasted manually, but I want such rows to appear in the second table automatically as I add data to the main table.
I’ve seen similar questions regarding Excel, with answers that involve writing some unique code for the sheet. Although my spreadsheet experience is rather basic I’m willing to try this, but not sure if it’s possible in Numbers.
I’ve seen similar questions regarding Excel, with answers that involve writing some unique code for the sheet. Although my spreadsheet experience is rather basic I’m willing to try this, but not sure if it’s possible in Numbers.
Response by poster: This does work, but not quite in the way I'm hoping for.
It copies all the rows from the main table into the second table — with only the relevant ones being filled with data — so there are many empty rows in the second table corresponding to the rows of the main table that didn't qualify under my criteria. Any idea how to prevent this and have the second table consist *only* of the rows that I want to be copied?
posted by theory at 4:49 PM on June 8
It copies all the rows from the main table into the second table — with only the relevant ones being filled with data — so there are many empty rows in the second table corresponding to the rows of the main table that didn't qualify under my criteria. Any idea how to prevent this and have the second table consist *only* of the rows that I want to be copied?
posted by theory at 4:49 PM on June 8
Best answer: I assume filtering out the empty rows is not an option?
As someone who is also more familiar with Excel than Apple’s numbers, I’m speculating that ISBLANK() could be combined with OFFSET() in some capacity. It may or may not be helpful to create table #3 with these functions, instead of trying to do everything on table 1.
posted by oceano at 8:18 PM on June 8
As someone who is also more familiar with Excel than Apple’s numbers, I’m speculating that ISBLANK() could be combined with OFFSET() in some capacity. It may or may not be helpful to create table #3 with these functions, instead of trying to do everything on table 1.
posted by oceano at 8:18 PM on June 8
Best answer: Any idea how to prevent this and have the second table consist *only* of the rows that I want to be copied?
I don't have Apple Numbers, but I've occasionally abused LibreOffice Calc to do this kind of thing using the IF(), INDEX(), ISNA(), MATCH() and OFFSET() functions, all of which I believe Numbers has.
I'm going to assume that the source table data is in cells $B$1:$E$99 for the purposes of this explanation; you should change that to something more readable but I don't know how Numbers handles assigning readable names to cell ranges so I won't do that here.
I'm also going to assume that there's a cell at $A$100 containing a value you want to select on, that instances of that value will turn up inside the first column of your source table, and that the constructed output table will occupy $A$101:$E$199.
First step is to construct a column that lists the row numbers within your source table from which you want values copied. Put the following formula in cell $A$101:
=MATCH($A$100,$B$1:$B$99,0)
That makes $A$101 contain the offset within your source column where the first instance of your selection value is. For example, if that value occurs on the fifth row of your source table, $A$101 will contain 5.
Next, put the following formula in $A$102 and fill it down to $A$199:
=MATCH($A$100,OFFSET($B$1,$A101,0,99-$A101,1),0)+$A101
This does similar work to the previous MATCH(), except that instead of searching your entire source column, it searches only those rows within it that are below the value it already found. The logic is as follows:
OFFSET returns a range of cells in much the same way as a range reference like $B$1:$B$99 would do. The first argument ($B$1 in this instance, the first cell in your source column) is taken as a reference point. The values of the second and third arguments ($A101 and 0 here) are taken as row and column offsets from the reference point that identify the top left corner of the range to be returned; the values of the third and fourth arguments (99-$A101 and 1 here) specify the height and width of that range.
For example, if the MATCH in cell $A$101 yields the value 6, meaning that the first matching item was found in the sixth row of your source column, the result of the OFFSET would be the range of cells from that column's seventh through 99th rows. Yes, seventh, not sixth. The values returned by MATCH are 1-based (a match found in the first row of what's being searched yields 1) while the row and column offset arguments to OFFSET are zero-based (passing 0 for either of those means "no offset", i.e. the top left corner of the resulting range will be exactly the cell used as the reference point).
The effect is to make the MATCH in this cell start where the MATCH in the cell above it left off, returning a row number for the next instance of the selection value. That row number, though, is relative to the start of the range returned by OFFSET, which is why the +$A101 is needed on the end to turn it back into a row number relative to the start of the whole source column.
There will be a row in column A below which the MATCH in that formula is not going to find any more instances of the selection value, and will start returning #N/A error values instead of row numbers.
Now we can use that column of MATCH results to fill in the output table. Put the following formula in $B$101 and fill it down and to the right, all the way to $E$199:
=IF(ISERROR($A101),"",INDEX(B$1:B$99,$A101,1))
How I'm hoping this will work is the same way IF(ISNA(...)) does on LibreOffice Calc or Excel, having just now found out that Numbers does not, in fact, appear to have ISNA(). The intent is to fill the cell with a blank value if the index in column A on the same row has a #N/A error value in it, otherwise to extract the appropriate cell from the source table. If Numbers missing ISNA() is only because it doesn't distinguish different kinds of error values, this should work.
I'll leave hiding the ugly indexing column to you.
posted by flabdablet at 7:29 AM on June 9
I don't have Apple Numbers, but I've occasionally abused LibreOffice Calc to do this kind of thing using the IF(), INDEX(), ISNA(), MATCH() and OFFSET() functions, all of which I believe Numbers has.
I'm going to assume that the source table data is in cells $B$1:$E$99 for the purposes of this explanation; you should change that to something more readable but I don't know how Numbers handles assigning readable names to cell ranges so I won't do that here.
I'm also going to assume that there's a cell at $A$100 containing a value you want to select on, that instances of that value will turn up inside the first column of your source table, and that the constructed output table will occupy $A$101:$E$199.
First step is to construct a column that lists the row numbers within your source table from which you want values copied. Put the following formula in cell $A$101:
=MATCH($A$100,$B$1:$B$99,0)
That makes $A$101 contain the offset within your source column where the first instance of your selection value is. For example, if that value occurs on the fifth row of your source table, $A$101 will contain 5.
Next, put the following formula in $A$102 and fill it down to $A$199:
=MATCH($A$100,OFFSET($B$1,$A101,0,99-$A101,1),0)+$A101
This does similar work to the previous MATCH(), except that instead of searching your entire source column, it searches only those rows within it that are below the value it already found. The logic is as follows:
OFFSET returns a range of cells in much the same way as a range reference like $B$1:$B$99 would do. The first argument ($B$1 in this instance, the first cell in your source column) is taken as a reference point. The values of the second and third arguments ($A101 and 0 here) are taken as row and column offsets from the reference point that identify the top left corner of the range to be returned; the values of the third and fourth arguments (99-$A101 and 1 here) specify the height and width of that range.
For example, if the MATCH in cell $A$101 yields the value 6, meaning that the first matching item was found in the sixth row of your source column, the result of the OFFSET would be the range of cells from that column's seventh through 99th rows. Yes, seventh, not sixth. The values returned by MATCH are 1-based (a match found in the first row of what's being searched yields 1) while the row and column offset arguments to OFFSET are zero-based (passing 0 for either of those means "no offset", i.e. the top left corner of the resulting range will be exactly the cell used as the reference point).
The effect is to make the MATCH in this cell start where the MATCH in the cell above it left off, returning a row number for the next instance of the selection value. That row number, though, is relative to the start of the range returned by OFFSET, which is why the +$A101 is needed on the end to turn it back into a row number relative to the start of the whole source column.
There will be a row in column A below which the MATCH in that formula is not going to find any more instances of the selection value, and will start returning #N/A error values instead of row numbers.
Now we can use that column of MATCH results to fill in the output table. Put the following formula in $B$101 and fill it down and to the right, all the way to $E$199:
=IF(ISERROR($A101),"",INDEX(B$1:B$99,$A101,1))
How I'm hoping this will work is the same way IF(ISNA(...)) does on LibreOffice Calc or Excel, having just now found out that Numbers does not, in fact, appear to have ISNA(). The intent is to fill the cell with a blank value if the index in column A on the same row has a #N/A error value in it, otherwise to extract the appropriate cell from the source table. If Numbers missing ISNA() is only because it doesn't distinguish different kinds of error values, this should work.
I'll leave hiding the ugly indexing column to you.
posted by flabdablet at 7:29 AM on June 9
Best answer: For that last step,
=IFERROR(INDEX(B$1:B$99,$A101,1),"")
(note IFERROR, not ISERROR) works on Calc, should work on Numbers as well, and is tidier.
posted by flabdablet at 8:26 AM on June 9
=IFERROR(INDEX(B$1:B$99,$A101,1),"")
(note IFERROR, not ISERROR) works on Calc, should work on Numbers as well, and is tidier.
posted by flabdablet at 8:26 AM on June 9
You are not logged in, either login or create an account to post comments
posted by oceano at 3:35 PM on June 8