0

For simplicity, let us consider a number columns that are filled with values generated by the SEQUENCE formula with a variable number of rows (common for all columns).

What I would like to obtain is another column array (with the same variable number of rows) where each row or element of the array is a TEXTJOIN formula of the elements of that row.

The first small issue is that while it is possible to refer to a single array, e.g. A1#, it is not possible to refer to a range of them and do something like A1#:F1#. It is somewhat inconvenient but I could add the individual arrays to the TEXTJOIN formula (A1#,B1#,C1#,D1#,...). [Correction: It is actually possible to do so]

The main problem is that I have not found any way to get the corresponding concatenated strings for each of the rows. I do not know if I have not found the way or it is simply not possible. A VBA alternative would not be ideal but could be considered as well.

ZZR
  • 49
  • 4
  • so in your instance:`=LET( m, A1#:B1#:C1#:D1#, spl,", ", rSeq, SEQUENCE( ROWS(m) ), L, MMULT( LEN(m)+(m<>"")*LEN(spl), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - LEN(spl), i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + ((rSeq-1)*LEN(spl)) + 1, IFERROR( MID( TEXTJOIN( spl, TRUE, m ), i, L ), "" ) )` – Scott Craner Aug 05 '21 at 17:35
  • But really all you would need is: `=A1#&", "&B1#&", "&C1#&", "&D1#` – Scott Craner Aug 05 '21 at 17:42
  • Yes, that does indeed work. It is not very elegant and it can be somewhat cumbersome when there are many columns, but it is probably the easiest solution. The first suggestion is perhaps too convoluted for this purpose, though it may be useful for more general cases and I did learn about the LET function, which I was not aware of. Seems quite handy for some complex formulas. Thanks. – ZZR Aug 05 '21 at 18:20
  • If you create the initial data with CHOOSE(): `=CHOOSE({1,2,3,4},SEQUENCE(10),SEQUENCE(10),SEQUENCE(10),SEQUENCE(10))` you can create a single point of reference then the `A1#:B1#:C1#:D1#` in the LET() would just be `A1#` Or you can use something like: `A1:INDEX(D:D,ROWS(D1#)` also in the LET() to refer to the whole range in fell swoop. – Scott Craner Aug 05 '21 at 18:32

0 Answers0