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'!
My first attempt
reduceV=
LAMBDA(array,
DROP(
REDUCE(
"",
SEQUENCE(ROWS(array)),
LAMBDA(a, n,
VSTACK(
a,
SCAN(, CHOOSEROWS(array, n),
LAMBDA(acc, v, acc & v))
)
)
),
1
)
);which gives
Not sure how to integrate it with timer.
Great - looks better. and likely faster too. For timing maybe replace "" by 0 (or 1) and '&' with '+' then update function name in timer formula. From the times, it looks like your system is running twice as fast! I needed to reduce to n=50 to make it responsive enough on my setup.
Yup looks pretty fast! In any case, native byrow/bycol extended functions would get my vote to bypass the nested array error in general, as suggested in the original post. Then other methods - including thunks - could benefit from being able to process arrays of results more efficiently.
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.