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'!
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'!
First - and obviously - thanks for you thoughtful reply. I'll play with both options against other examples, but my gut feeling is that #2 will come up ahead.
Second - and this is sort of a general rant - having played around with many of the new shiny toys recently introduced to Excel, I must say the whole process feels strange, like "two steps forward - one step back". This one is a great example of Excel being inconsistent in applying its own logic: if something can be done by row (or by column) it should be always doable that way, instead of letting users find out (and memorizing) the circumstances where the logic works and where it doesn't. When you say, about the two approaches (in this case) to overcoming the problem, that "neither of which is that attractive" you are being, I feel, very polite. The near constant need to look for "alternatives", "workarounds", "hacks", etc. to resolve issues which should be straightforward is utterly exhausting....
Second - and this is sort of a general rant - having played around with many of the new shiny toys recently introduced to Excel, I must say the whole process feels strange, like "two steps forward - one step back". This one is a great example of Excel being inconsistent in applying its own logic: if something can be done by row (or by column) it should be always doable that way, instead of letting users find out (and memorizing) the circumstances where the logic works and where it doesn't. When you say, about the two approaches (in this case) to overcoming the problem, that "neither of which is that attractive" you are being, I feel, very polite. The near constant need to look for "alternatives", "workarounds", "hacks", etc. to resolve issues which should be straightforward is utterly exhausting....
I see your point. The best I can say is that the pitfalls are relatively few.
A bit like traditional Excel where one tries to explain why
= COUNTIFS(MONTH(date),6))doesn't work. Because CONTIFS doesn't accept array parameters in place of a range reference always sounded a bit lame.
In the present case, I got to
= MAKEARRAY(2,3, Expandλ( BYROW(data, LAMBDA(row,Thunkλ(SCAN("", row, Concatλ)))) )) where Expandλ = LAMBDA(ϑ, LAMBDA(r,c, INDEX(INDEX(ϑ, r,1)(),,c) )); Concatλ = LAMBDA(array,cell, array&cell);It is heavy going but, once done, it can be reused. Hopefully, not too often!
As far as I can tell, I am not one of the 10,000 Excel users who rate as a developer by the ICAEW (referred to in your other answer), but I AM a long time, heavy user of Excel so I feel qualified to offer my two cents on this:
Having studied your approaches more closely, I have to say that I now tend to prefer your first approach (designated as "Single sequence" in your attachment). Still, the approach involves using 8 different functions (SCAN, SEQUENCE, LABMDA, LET, QUOTIENT, MOD, INDEX and IF)!
The solution proposed by mtarler - which also works - involves even more (LET, ROWS, COLUMNS, TEXT, SEQUENCE, SCAN, LAMBDA, IF, MOD, LEFT and MID) - 11 in total!
Just eyeballing these solutions (let alone parsing them) would give your run-of-the-mill non-developer user a headache...
What I imagine that type of user would do is just give up on spillable purity, use instead
=SCAN("",A1:C1,LAMBDA(a,b,a&b))and then drag it down one row - and be done with the whole thing.
Found it! This discussion covers much the same ground as one I initiated in the past.
Accumulating arrays - Microsoft Tech Community
I did have to create 'ConcatThunkλ' by substituting a '+' by '&'. in order to apply the previous function to the new situation!
= AccumulateThunkλ(charArray, charInit, ConcatThunkλ) where "ConcatThunkλ" = LAMBDA(accϑ, strϑ, Thunkλ(accϑ() & strϑ()));
it seems like your first concept was a cleaner method. Use scan and sequence to perform the scan and a 'reset' for each row something like:
=LET(in,A2:C3, r,ROWS(in), c,COLUMNS(in), s,TEXT(SEQUENCE(r,c,0),"000")&in, SCAN("",s,LAMBDA(acc,k, IF(MOD(--LEFT(k,3),c),acc,"")&MID(k,4,999)) ) )
ok after trying the original idea of using an index() inside the lambda of the original in I got the nested error and had to modify to this 'less attractive' version of concatenating the 'index array' with the 'array of values'.Sorry for the edit, I made a few tweaks to make it more generalized.
I still find this class of problem intensely difficult to get my head round and I am frequently guilty of 'reinventing the wheel'. Unless the solutions can be packaged in the form of meaningful Lambda functions, I suspect the methods lie well beyond the pay grade of the normal Excel developer - and that bearing in mind the assertion of the ICAEW that fewer than 1 in a 1000 Excel users would rate as a developer!
All sorts of thoughts float around. For example, the accumulation may be by concatenation (text), by addition (numbers) or even by multiplication (indices of multidimensional arrays). Perhaps the operator itself should be a Lambda variable passed into the calculation framework as a parameter (Concatenateλ, Sumλ, Productλ).
You were unhappy with the requirement to concatenate the 'index array' with the 'array of values'. To the best of my knowledge, there is no perfect solution. For text, a comma-separated list could be used to hold the index/value pair, for numbers, it could be a complex number. A thunk provides a reasonably standard, if not simple, approach and I think a more normal Lambda function would also be possible, whereby Pairλ(1) is the index and Pairλ(2) the value (INDEX would be inside the Lambda function but the function itself can be passed as a scalar assuming the parameter is missing).
Something else I forgot when identifying the possible approaches, was that the second approach of thunks has two distinct implementations. One builds a column of thunks, each containing an accumulated row, whilst the other builds a row of thunks, each containing a column array.
BTW. My use of 'λ' within the naming might cause confusion for many, who do not use the Greek alphabet to express ideas, but name manager accepts the symbol as just another letter within the name. Since it is not present as a character on an English language keyboard, I use Autocorrect to convert '\lambda' into 'λ', rather like the equation editor in Word.
