Forum Discussion
Can SCAN() and BYROW() be combined?
- Mar 11, 2022
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'!
I could agree, but everything depends on goals and concrete scenario.
I have seen the posts but have yet to work through them in detail. Meanwhile I have tried VSTACK as an alternative to MAKEARRAY, but still using Thunks. The worksheet formula relies on a number of other named Lambda functions.
= Expandλ(BYROW(data,Scanλ))
Scanλ
= LAMBDA(row,Thunkλ(SCAN("",row,Concatλ)));
Concatλ
= LAMBDA(a,b,a&b);
Expandλ
= LAMBDA(dϑ,
REDUCE(,dϑ,LAMBDA(accϑ,ϑ,Thunkλ(VSTACK(accϑ(),ϑ()))))()
);- PeterBartholomew1Mar 26, 2022Silver Contributor
No, I need to find some time to work through Lori's solution. I did some timing runs with my solution using 100x100 and 200x50 grids of single characters using Charles Williams's profiler. The latter was worse for a range recalc. with timings of 350ms rather than 250ms.
- SergeiBaklanMar 26, 2022Diamond Contributor
PeterBartholomew1 , I had no doubt you will use thunks. Did you try to integrate with lori_m timer?