1

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?

enter image description here

SoftTimur
  • 5,630
  • 38
  • 140
  • 292
  • I think you can easily reuse the formula that causes the spill and wrap it in textjoin: `=TEXTJOIN(",",1,YourFormulaInA2)` – P.b Jun 30 '21 at 17:39
  • @P.b `YourFormulaInA2` does not cover columns B to E. – SoftTimur Jun 30 '21 at 17:43
  • I recently fell into the same need when trying to create a solution for all combinations of a variable element array. I finally made a ridiculous brute force solution that broke out each column such that the formula had to be rewritten for each set of column dimensions (e.g. 3, 4, 5...). I never found a solution, so I will be watching this one. Good luck - it's a great problem to solve. – mark fitzpatrick Jun 30 '21 at 18:18
  • @markfitzpatrick let's see... – SoftTimur Jun 30 '21 at 18:20
  • what I just posted has a flaw - it assumes 1 character cells. will think about how to go around that. – mark fitzpatrick Jun 30 '21 at 20:14
  • @SoftTimur - I looked at nearly the same problem only just last weekend. I gave up in frustration and thought that I would not find an answer. But as soon as I said to you that i could not solve it, bookmarked it and closed the page, the "what if you parsed a giant text blob" idea came to me. I built a LET formula once that parsed text according to break lines, so I know how that works, so... Your problem is a good general archetype. Thanks for a well thought out problem. Hope it helps others. – mark fitzpatrick Jun 30 '21 at 21:19
  • Looking back, my answer may not be *The Answer*. Is there a requirement that the input be A2:E2? If so, my answer would need to discover the spilled columns from their root cells. If the **answer requires A2:E2 as input** and it is **acceptable to have a max number of columns**, there is a solution. **If A2:xxx2 must be the input** where **there is no fixed limit to xxx**, the problem gets hard (again). What do you think? As for solving my earlier problem, the solution below now does that as my input was a single, 2-dimensional dynamic array of variable size. – mark fitzpatrick Jul 01 '21 at 06:27
  • I just thought of a way to have A2:xx2 as a range of roots to dynamic arrays that can be the input to your formula. I will append it to my answer below. – mark fitzpatrick Jul 01 '21 at 08:01
  • @SoftTimur - did you even read the answers? – mark fitzpatrick Jul 30 '21 at 05:52
  • @markfitzpatrick, hi Mark, I did read the answers. And I do appreciate your help in every question. For this question, I did have a little bit of hesitance, that's why I did not accept anything immediately... It does happen... – SoftTimur Jul 30 '21 at 08:57
  • Thanks @SoftTimur - just no feedback until now. – mark fitzpatrick Jul 30 '21 at 10:45
  • Cheers, ST! :-) That is probably the only referential work I've done here. The question is great and it keeps showing up time-after-time. It is not immediately evident, but to answer [this](https://stackoverflow.com/questions/68812918/how-to-summarize-data-with-each-data-change/68824642#68824642) one requires an answer to your original question. – mark fitzpatrick Aug 30 '21 at 10:10

1 Answers1

3

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.

enter image description here

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.

LAMBDA METHOD

So... finally LAMBDA is here and the above problem can now be solved by this single line formula:

=BYROW(A2:E7,LAMBDA(x,TEXTJOIN(",",1,x)))

crazy...

mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
  • 1
    Just brilliant! – P.b Jun 30 '21 at 20:23
  • Merci, @P.b - really appreciate the encouragement. I may have found a way to fix the variable length cell values. Getting real close at the moment. – mark fitzpatrick Jun 30 '21 at 20:30
  • 1
    PS if you wrap it in `=IFERROR(brillianformula,"")` it throws a blank value if one row doesn't contain values (even the first row in range). – P.b Jun 30 '21 at 21:07
  • Cheers @P.b! I just saw your comment after I posted the new version. I will edit again and add the IFERROR. How did you even find that? Great stuff! – mark fitzpatrick Jun 30 '21 at 21:10
  • I was just trying out your formula for better understanding and tweaked the data a bit. Cheers on finding the solution you had in mind. – P.b Jun 30 '21 at 21:29
  • 1
    Holy crap!!!. I would never have thought of spliting the TEXTJOIN result to make it spill correctly. I tip my hat to you sir. – Scott Craner Jul 30 '21 at 16:08
  • WOW! I have screenshot this one. - Cheers, @ScottCraner - I had actually given up on this one and then it occurred to me "what if"... – mark fitzpatrick Jul 30 '21 at 16:41
  • BTW, I found this because your answer was linked [HERE](https://stackoverflow.com/questions/68593938/row-wise-textjoin-of-dynamic-array-with-lookup/68594284#68594284) – Scott Craner Jul 30 '21 at 16:49
  • @ScottCraner This is really quite a circle, because the original inspiration was driven by this related OP https://stackoverflow.com/questions/67905393/how-to-generate-all-combinations-using-5-columns-using-a-formula-in-excel/67926177#67926177 that referenced your original work. It has the same base problem, but I found no solution. This above OP helped me find the solution, but there remains one more problem to solve, which you already commented --> https://stackoverflow.com/questions/68400019/excel-dynamic-array-formula-to-create-a-running-product-of-a-column – mark fitzpatrick Jul 30 '21 at 17:16
  • Afterwards, by chance, I stumbled upon this OP https://stackoverflow.com/questions/67190244/use-textjoin-with-filter-and-reference-to-a-spill-range/68312099#68312099 and used the above answer to address it. – mark fitzpatrick Jul 30 '21 at 17:19