excel formula - Reference for a dynamic range

Question

I have a table A2:E7, where each column comes from a dynamic array formula. As a result, =A2#, =B2#, etc. work and spill.

Now, I would like to use TEXTJOIN over each row, the expected results are in column F. But =TEXTJOIN(",",TRUE,A2:E2#) does not work. I don't know how to write the dynamic reference for a range.

Could anyone help?

Answer 1

Previous Answer

this one has a flaw: it assumes 1 char cell values, but I am leaving it here for reference. New answer below.

You could do:

=LET( m, A2:E7,
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m),SIGN( SEQUENCE( COLUMNS(m) ) ) )*2-1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq - 1,
       MID( TEXTJOIN( ",", TRUE, m ), i+1, L ) )

Revised Answer

This can take variably sized cell values:

=LET( m, A2:E7,
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - 1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
       IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) )

added @P.b's IFERROR wrapper to prevent blank rows from throwing a VALUE error. - Thanks P.b! Nice catch.

There may still be some places for optimization. Basically, it does a giant TEXTJOIN at the the end that concatenates everything with "," delimiters. With that in mind, it prepares some arrays that will be used to break up the giant text blob. L creates an array of lengths of each cell value as well as its delimiter (less 1). i is an index that simply adds up L's values consecutively into a columnar array to tell the MID function where to break while L tells MID the size of each chunk of the giant text blob.

NB: If the delimiter is more than 1 character, this fails.

Expanding Roots Method

If the requirements are:

• The input must be a row of cells that each contain dynamic arrays that are spilled below.
• The number of columns is variable, but contiguous.
• The delimiter is one character.
• All input cells are dynamic arrays of one column dimension.
• All dynamic arrays are equally sized.§

Then this formula should work:

=LET( root, A2:E2,
       c, COLUMNS(root),
       m, IFERROR( INDEX(root,1,1):INDEX(root,1,c)#, "" ),
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( c ) ) ) - 1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
       IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) )

where root (A2:E2) is the input range that contains the roots of each dynamic array.

§ - If they are not equally sized, the underlaps will contain 0's. This can be fixed by replacing 0 with "", but if your inputs would normally contain valid 0's, this would be a bad approach, so I left that out and maintained a requirement of equal length dynamic arrays as inputs instead. If you require variable length arrays, with zeros, it's possible, but will add more steps that could slow it down.