Can SCAN() and BYROW() be combined?

[TheDub]

The support page for SCAN() contains the following example: 

                                      =SCAN("",A1:C2,LAMBDA(a,b,a&b))

My general issue is combining BYROW() or BYCOLUMN() with other functions, but I'll illustrate it with this SCAN() example:

How does one SCAN() row by row such that the output it something like:

a, ab, abc

d, de, def

=BYROW(A1:C2,LAMBDA(row,SCAN("",row,LAMBDA(a,b,a&b))))

doesn't work...

[#2]

@TheDub @Peter Bartholomew 

Here's another abomination of an approach for combining SCAN() and BYROW():

=TEXTSPLIT(TEXTJOIN("~",TRUE,BYROW(A1:C2, LAMBDA(array,TEXTJOIN("|",TRUE,SCAN("",array,LAMBDA(a,b,a&b)))))),"|","~")

It gets around the type incompatibility by joining arrays into strings, processing them, then splitting the string back into an array.  This example uses "|" as a column separator and "~" as a row separator.

I was only doing this as a means to an end, and did not optimise much - hopefully someone finds a way to make it such that there is only one TEXTJOIN.

A drawback of this approach is that everything gets compressed into a single string. This makes it hard to follow, and I have not checked but there is likely to be a limit to the length of the string that can be processed properly.

Like all the other approaches that I am aware of, it is unwieldy and I would prefer not to burden others with spreadsheets that contain this.

Has anyone found any better approaches since this thread was active a couple of years ago?

[#3]

@Levi Bailey 

Hi Levi.

I wouldn't say that much has changed.  It really is up to Microsoft to sort the mess that is of their making.  I think the problem of helper functions not returning arrays of arrays is getting more important day by day.  If one measures the need by the proportion of problems for which the array of arrays is a central feature of the problem which, as the scope of dynamic array formulas increases, may occur several times within a formula and resolving the issue becomes more important.  Mind you, I would not expect ideas such as @Craig Hatmaker 's 5g functions to fall within the original specification.  Most spreadsheet users still are attached to horrible 'and send down' relative references, so it is only a minority that require the DA changes to be brought to fruition.

I had made some progress towards overcoming the problem by using Thunks to hold the inner arrays, and having other user functions specifically written to expand the arrays of thunks to display the content.  

A generalised Lambda helper function that return arrays of arrays using bisection. - Microsoft Commu...

I still have some ideas to follow up, for example, expanding and stacking thunks 16 at a time, so 5 REDUCE steps would process an entire Excel column.  The challenge is to both achieve acceptable efficiency and to hide the unsellable contortions from the end user!

[#4]

@Levi BaileyI found a way using MakeArray

=LET(data,
  SEQUENCE(2,3),
  MAKEARRAY(ROWS(data),COLUMNS(data),
    LAMBDA(row,col,
      CONCAT(
        INDEX(data,row,SEQUENCE(col))
      )
    )
  )
)

Found it on this YouTube video

[#5]

@karimbaez 

As discussed above that's the slowest variant from all possible ones.

[#6]

@TheDub 

My approach with SCAN:

ScanByrow
=LAMBDA(matrix,LET(
    counter, SEQUENCE(ROWS(matrix)),
    data, HSTACK(counter, matrix),
    AccumulateText, LAMBDA(a, v, IF(ISNUMBER(v), "", a & v)),
    Texts, SCAN("", data, AccumulateText),
    DROP(Texts, , 1)
))