Can one automate table creation by directing autofill to increment row numbers, and perhaps also add a blank column after each entry, as seen in screenshot sample?
[Re-Titled by Moderator]
Original Title: TEXTSPLIT; incrementing row numbers; Numbers 14.4; autofill
iMac 24″, macOS 15.5
Alternatively, you can create a formula that pulls data every other column and then you won't need the index numbers. It will create the empty columns for you.
B2 =IF(ISEVEN(COLUMN()),OFFSET(Worthy Images::$A2,0,COLUMN()÷2),"")
Fill right to complete the row.
If the column is even it pulls data from column number column/2. Otherwise it returns "".
or B2 =IF(ISEVEN(COLUMN()),INDEX(Worthy Images::2:2,0,COLUMN()÷2+1),"")
There is probably a more clever or elegant way to do it with some of the new functions.
Alternatively, you can create a formula that pulls data every other column and then you won't need the index numbers. It will create the empty columns for you.
B2 =IF(ISEVEN(COLUMN()),OFFSET(Worthy Images::$A2,0,COLUMN()÷2),"")
Fill right to complete the row.
If the column is even it pulls data from column number column/2. Otherwise it returns "".
or B2 =IF(ISEVEN(COLUMN()),INDEX(Worthy Images::2:2,0,COLUMN()÷2+1),"")
There is probably a more clever or elegant way to do it with some of the new functions.
The row numbers you can do with a formula like
=IF(A<>"",ROW()-1,"")
This will spill down to the entire column
The empty columns you can do by hitting Tab twice instead of once after making an entry. Three times if it needs to create a new column.
Ahh, I see. Your formula(s) in row 2 are readjusting when you insert a new column, making it look like you added the column to the far right. I cannot think of a formula structure that will prevent that from happening other than having another row with "index" numbers in it. 1 would be column B, 2 would be column C, etc. Below is a simple example to get you started. The formula is probably not exactly what you want, it is just an example.
The 1,2,3, etc are "actual" not formulas.
When you insert a new column in the middle, that column will not get a number.
Maybe by script.
But why would you want to do this?
In general you are better off with data in true tabular format, not interspersed with blank columns or rows.
If you haven't do so already be sure to have a look at the templates at File > New for good examples of effective document and table layout in Numbers.
SG
Does "cannot add a blank column" mean "Numbers will not allow me to add a blank column" or "I am not getting the results I expect when trying to add a blank column"? I assume it is the second one. I assume you are getting an error triangle when making a blank column. You could just delete the formula from the cell with the error triangle or you can use this formula that handles it.
=IFERROR(TEXTSPLIT(B2,,",",ignore-empty,case-sensitive),"")
I'll have to look into scripting.
The blank columns will be filled with filenames of yet-to-be-created copies of corresponding frame numbers.
Thanks for tip re: templates-- haven't looked in a long while.
Interesting how that IF example works, but I still don't know how to apply it to step or increment the row# in the first argument of my TEXTSPLIT formula, above, for using autofill to move across successive columns...
What's key here (and missing) is what is in Worthy Images::F2?
Is that an entire string in a single cell, like:
or is it the starting point of a range of cells where the values are broken out, like:
There are probably combinations of TEXTSPLIT(), TRANSPOSE(), TAKE() and other functions that will help, but knowing what we're starting with (and how flexible that input is) will help a lot.
Thnx, just saw your question; F2 is an entire string, like in your first example. In this case, F2 is just "0,1" two Worthy photo neg. numbers.
All entries in column F are similar, comma-delimited lists, of varied length.
By now, I've two versions of the file, and find, when trying to autofill from a column of concatenated attributes including the neg #s from Worthy Images, the first spills down properly, but an autofill won't go look to the next row. This is true whether Preserve Row is checked or not...
Unfortunately that didn't clarify things for me.
First you say:
> F2 is an entire string, like in your first example
(which I interpret as "F2 is 'Neg #, 0,1,8,+others, 9, 33...'")
Then you say:
> In this case, F2 is just "0,1"
So which is it?
Maybe a screenshot of your source table would help clarify, especially with the desired result.
For example, if Worth Images looks like:
Then a simple TEXTSPLIT() could get you:
It is more like your second meaning, **: The red triangle appears momentarily, one column to the right, where we want the blank column, but immediately jumps to the right end of the table, where a new column is placed. That same error triangle ends up in that column.
Please see my screenshot from 6/15, to Camelot, for what the table looks like before the above-mentioned attempt.
Previous problem solved, above, but now, even without any row or columns preserved ($), I cannot add a blank column after even the first entry.
[Edited by Moderator]
How can I use TEXTSPLIT for incrementing row in Numbers?
