Forum Discussion
Handling an array contining the same element multiple times
One of the things (among quite a few) I find frustrating with the new-generation functions in Excel is their occasional limited scope. Two examples are XLOOKUP() and XMATCH: it took me a while to und...
In my most recent post on this thread, I had got to the point of returning the result for all names as a single array formula through the use of thunks. The obvious shortcoming was that the number of rows and columns output was set to 2x2 and required the user to modify the formula. A version of the formula that extracts the size of the output from the thunk using
...
n, ROWS(achievedϑ),
m, MAX(MAP(achievedϑ, Countλ)),
MAKEARRAY(n,m, Expandλ(achievedϑ))
where Countλ refers to
= LAMBDA(x, COUNT(IF(TYPE(x)=128,x(),x)))
If Countλ is passed an array, it performs the count; if it is passed a thunk, it expands the thunk before counting.
There are times reading through message threads like this when I definitely feel like Charlie Brown in this classic comic strip.
- Do we each have a rôle to play? One to shape the clouds and another to divine their significance?
I can't help but feel that there is a change of mindset required to exploit the new functionality to best effect. Before, I would have been 'authoring a formula' (albeit using a highly idiosyncratic syntax), now it is more a case of writing a short program module. However, I do not like the need to resort to 'thunks', other than as a last resort to address 2D arrays of row vectors for example. A simple column of row vectors should just display on the worksheet without trickery.