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'!
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ϑ()));
Well, I'm still trying to parse your function for charArray - I can already see that it involves a bunch of nested lambdas which in turn contain quite a number of functions (BYCOL, SCAN, MAKEARRAY, etc.)...
I don't think I'm qualified to have an opinion about whether Excel is now Turing-complete or not (whatever that practically means), but this seems a lot of work for something that in Python, for example, can be accomplished in exactly two lines, one of which is an import statement.
BTW, I actually did see and read the discussion you link to - but didn't make the connection; I believe I was (and probably still am) so annoyed by the failure of BYROW to work with SCAN, I couldn't see what was right in front of me!
The is no need to convince me that the limitation on array of arrays/ranges is both over-restrictive and is now causing difficulties. I have made such points in the past and JoeMcDaid explained that Excel never has accepted such data objects; it is just that now one is far more likely to encounter this limitation.
At a more mundane level, one can see that
= XLOOKUP(lookupVals,Table1[Name],Table1)fails, with the output truncated, whilst
= INDEX( Table1, XMATCH(lookupVals,Table1[Name]), XMATCH(Table1[#Headers],Table1[#Headers]) )works, so the problem has earlier manifestations. What Joe would need is a compelling case for Microsoft to commit resources to the problem and confidence that any change will not cause backward compatibility issues. My feeling is that it may be sufficient to deal with the problem within the restricted context of the BYROW, BYCOL and MAKEARRAY operators, in order to avoid wider unintended consequences. After all, for years, SUMPRODUCT (and, I think, AGGREGATE?) broke the rules and allowed array calculation without needing CSE to override implicit intersection, so the idea of exempting specific functions from the rules is not without precedent.
The other thing you need is real-world examples which have been frustrated by the failure of the new helper functions to perform the tasks that they obviously should be capable of. I think 'send a frown' gets attention, especially if key words channel the query to the correct team.
As for the Python 'include', I would prefer to wait and see a solid level on performance at the level of program abstraction currently available to Excel developers before considering even more abstract layers of helper functions.
What I am currently doing is to examine the limitations and workarounds, as well as the new opportunities, trying to determine which routes provide best practice. Nobody would be more pleased than I, if the need for 'too clever by half' solutions were suddenly to disappear. At least, the practice of building solutions with Lambda functions can conceal the detail and leave more coherent code at user level.
While I understand (I hope) what you are saying and even agree with most of it, I'd like to respond to two statements you make:
the idea of exempting specific functions from the rules is not without precedent.While I appreciate the power of precedent, the fact that a bad idea is not without precedent doesn't necessarily turn the idea from bad to good.
What Joe would need is ... confidence that any change will not cause backward compatibility issues.I'm afraid that ship has already sailed. As a matter of fact, it's not too farfetched to imagine that Excel will one day be forked into incompatible Excel Classic and Excel New Age...
