Forum Discussion

TheDub's avatar
TheDub
Iron Contributor
Apr 27, 2022

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...
Formulas and Functions
TheDub's avatar
TheDub
Iron Contributor
Apr 27, 2022

You are absolutely right about running into the very dreaded nested array problem...

By way of illustration, I tried to take OliverScheurich's answer (and the same goes for Patrick2788's answer) and filter out the spaces from the output. That called for combining FILTER() and BYROW() this way

 

=BYROW(answer,LAMBDA(myrow,FILTER(myrow,myrow<>"")))

 

or, since we're playing games, in reverse:

 

=FILTER(answer,BYROW(answer,LAMBDA(myrow,myrow<>"")))

 

Sure enough, a #CALC error popped up with the message "Nested arrays are not supported"!

 

I'm going to turn now to playing with your answer and see what I get, but - off the bat - I have to say that the idea adults (presumably) like us have to resort to workarounds like "turn the row result into a single concatenated value" to get to the promised land makes me shake my head (or SMH, as they say nowadays....)

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 27, 2022

TheDub 

I feel slightly embarrassed to offer this solution to your problem.  It shouldn't be this complicated!!!

= LET(
    achievedϑ, BYROW(table,
      LAMBDA(record,
        Thunkλ(SORTBY(IF(record=target, year, ""), record<>target)))),
    MAKEARRAY(2,2, Expandλ(achievedϑ))
  )

where forming and expanding the Thunk is achieved by

Thunkλ 
= LAMBDA(x,LAMBDA(x));

Expandλ 
= LAMBDA(achievedϑ,LAMBDA(r,c,
    INDEX(INDEX(achievedϑ,r,1)(),c)))

What it does is to replace the 1D array of 1D arrays by a 2D array of scalars.  The latter is legal whereas the former is a sin beyond comprehension!  JoeMcDaid ?

 

Resources