Forum Discussion
Handling an array contining the same element multiple times
The problem you describe is remarkably difficult because you hit the dreaded nested arrays problem. The first stage is reasonable OK. Given a single record,
= FILTER(year, record=target)
identifies the years the target is achieved. The catch is that as soon as you try to use BYROW to create a 2D output array, Excel throws an error. One way of evading the problem is to turn the row result into a single concatenated value. Then the BYROW step works.
= BYROW(table,
LAMBDA(record,
LET(
yrs, FILTER(year, record=target),
TEXTJOIN(", ",, yrs)
)
)
)
To get an array of arrays requires MAKEARRAY to expanding an inner Lambda function (probably a thunk - that is a Lambda function that takes a null parameter string).
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....)
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 ?
