Forum Discussion
Getting multiple values against unique names...
It is also possible to return a list of arrays, each containing the values corresponding to one of the names. It is, unfortunately, made insanely difficult by an incredibly bad decision made by Microsoft when specifying the new array functionality. Namely, nested arrays were to be treated as errors rather than a fundamental building block of building solutions.
In the present case one should be able to use MAP to run through the distinct names and have Excel collect the filtered value arrays into a 2D array. Such logic did not prevail and, instead, unpleasant workarounds are needed.
= LET(
distinctName, UNIQUE(Table1[Name]),
array, REDUCE("", distinctName,
LAMBDA(acc,n,
VSTACK(acc, TRANSPOSE(FILTER(Table1[Value], Table1[Name]=n)))
)
),
IFERROR(DROP(array,1),"")
)It is possible to pretty the formula up a bit, but only by plunging into even more advanced techniques to solve a problem that should have been well within scope for the native functions.
The steps to tidy the formula up are insanely complicated for such a simple task (or maybe it's just me getting senile!). To use any built-in function within a helper function, the built-in function needs to be wrapped as a Lambda function.
That is OK,
Filterλ
= LAMBDA(arr, crit,
LAMBDA(acc,n,
VSTACK(acc, TRANSPOSE(FILTER(arr, crit=n)))
)
);but the presence of VSTACK suggests that all is not well. Next, the REDUCE element of the formula needs to be wrapped in a further Lambda function to hide the complexity. The intent of the REDUCE/VSTACK combination is to perform a MAP operation without triggering the 'nested arrays' error. The worksheet formula is now
Mapλ
= LAMBDA(name, Fnλ,
REDUCE(hdr, name, Fnλ)
);The other trick is that the partially satisfied Filterλ Lambda function (itself a Lambda function is passed as a parameter to Mapλ, giving a final form for the worksheet function,
= LET(
array, Mapλ(UNIQUE(Table1[Name]), Filterλ(Table1[Value], Table1[Name])),
IFERROR(DROP(array,1),"")
)which is far more complicated than it should be.
- Thanks Peter. This answer is way beyond my level of competence. But I hope your insights will be useful to anyone else following this thread. Cheers!
- If you have access to Excel 365 we could set you on your way far more gently than this! The new functionality is remarkable but I can well understand your hesitation.
