Forum Discussion
Can SCAN() and BYROW() be combined?
It looks like it should work and, indeed, a similar formula with REDUCE does work. Unfortunately SCAN produces an array on each row, leading to an array of arrays which hasn't been implemented within the calc engine. There are two distinct approaches to overcome the problem, neither of which is that attractive.
The first is to allow SCAN to run through the 6 values as a single sequence but to reset the resulting text to null whenever the scan returns to the first column. Rather than scanning the text array, I scanned the sequence {0,1,2;3,4,5} which can both be used to look up the text to concatenate and to identify the leading column.
= SCAN("", SEQUENCE(2,3,0), LAMBDA(str,k, LET( row, 1+QUOTIENT(k,3), column, 1+MOD(k,3), chr, INDEX(data, row, column), IF(column>1,str,"")&chr ) ) )The second approach follows your original formula more closely but, instead of allowing SCAN to build a text array, it builds a Thunk that contains the array. BYROW then creates an array of 2 thunks and if the result is passed to MAKEARRAY each thunk may be singled out by INDEX, then expanded by passing it a null parameter string and wrapped within a further INDEX, which will return a single value to be assembled into the solution array.
Thunkλ = LAMBDA(x,LAMBDA(x)) Forming a thunk: = LET( arrayϑ, Thunkλ(array), Returning its content: = arrayϑ()I will leave it to you to judge whether the most appropriate answer to your question is 'yes' or 'no'!
TheDub was pretty irritated by the message "Nested arrays are not supported", and I believe rightly so. Most Excel errors report an error on the part of the user/developer. This one is different. The correct answer to a wide class of problems to which one might with to apply dynamic array methods is the array of arrays. This error is nothing to do with the user, it is simply flagging up a deficiency in the code; one which may not have mattered in the past but which is now centre stage.
As a minimum, BYROW, BYCOL and MAKEARRAY should address this limitation and convert the 'column of row arrays' to the equivalent 2D array without involving the user in workarounds.
Agreed, the previous post was intended to be fully in support of the OP and to reiterate that the proposed BYROW formula should really be made to work in a future update if possible.
I'd really prefer that nested arrays were converted to 2d arrays natively. Google sheets does this eg:
={{1,2};{3,4}} -> {1,2;3,4}
={{1,2},{3,4}} -> {1,2,3,4}
so no need for separate VSTACK / HSTACK functions. I gather from the response you received from the development team that backward compatibility makes such an update more challenging however.
Oops, I didn't open the link within your file. Yes, looks like unchanged samples from the prototype.
Prioritization is never simple task. I guess Microsoft decides such compromises every few months (have no idea about their planning process). If to simplify, like make Evaluate Formula box resizable or introduce few other lambda functions.
My another guess on first place is what demanded by corporate business which gives 85% (third guess) of income.
Anyway, sooner or later nested arrays shall appear.
The timer code is courtesy of the MS Research team and was used for prototyping some of the new functions. Without access to the new functions I substituted the vstack prototype function from that link and got the results shown in attachment which are a little disappointing and I'm sure thunk results could be improved significantly though unlikely ever to match the faster alternatives listed.
I've no idea how much effort would be required to address the nested arrays error but community feedback like this can only help bump it up the priority list!
Afraid implementation of array of arrays requires re-building of calc engine architecture. Other words create new Excel, if not from scratch but close to that. Perhaps I'm wrong, just my impression.
Other way is to built in workarounds like we use now in built-in functions, but that will be quite narrow solution which works for concrete cases only.
Anyway, I believe this or that solution will be found and we'll see array of arrays one day. Such functionality is quite demanded for new Excel.
