Forum Discussion
Solved
Can SCAN() and BYROW() be combined?
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 ...
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'!
so i tried to follow the discussion. Can someone maybe describe what that Thunk function is/does. It appears to be just a way to pass an array without it knowing it is passing an array? maybe? a lambda that returns a reference to a lambda? who would have even thunk that would work?
"Who would have even thunk that would work?" Who indeed? (grin)
It wasn't immediately obvious to me that the concept could be of any use. I sometimes think of it as the computing equivalent of the freeze dried survival meal. In its packaged state, it doesn't look like food but add water and bring to the boil and it is reconstituted as if by magic.
Before making a determined effort to exploit Thunks, I had used basic Lambda functions for similar purpose with some success, for example
"Arrayλ"
= LAMBDA(k,INDEX(Array,k))can be passed as a Lambda variable, but
= Arrayλ({1;2;3})will return the first 3 terms of the array. This is actually useful when one is handling multidimensional arrays where expressions like
= Arrayλ(3,1,2)
"Arrayλ"
= LAMBDA(i,j,k, INDEX(Array,i+10*(k-1), j))will successfully use the 3 indices pick terms from the grid.
The Thunk is a bit more obscure
"Arrayϑ"
= LAMBDA(Array)
"Thunkλ"
= LAMBDA(x,LAMBDA(x))
"Arrayϑ"
= Thunkλ(Array)
"Worksheet formula"
= Arrayϑ()The first term is a thunk, but used inside SCAN will return the array and not the thunk. To do that one can define a Lambda function Thunkλ with 2 Lambdas (thanks tboulden). Apply it to the array and it correctly returns the Thunk. Add a null parameter string and you magically reconstitute the array (just like the pack of "MaryJanesFarm Kettle Chili"!