Dec 11 2021 07:18 PM
How can an array be filtered row by row?
I have an array that has names and FALSE as entries. I'm trying to remove the FALSE entries. FILTER does not appear to let me work each row separately, and LAMBDA does not allow output of an array, only a scalar.
I'm looking for an array solution, not a copy-down.
Is this possible?
I've attached an example.
(I truly am mystified by the FILTER function's behavior with array parameters. A single row works fine - FILTER(onerowarray,onerowarray<>FALSE) - but multiple rows produce #VALUE.)
Dec 12 2021 02:21 AM
Why do you need IF() statement and then filter each row with FALSE value?
Using filter you can achieve it easily.
=TRANSPOSE(
FILTER(Projects[Volunteer],Projects[Project]=H15))
Dec 12 2021 11:28 AM
You may sort entire 2D spill row by row or column by column. Samples are
(11) Excel, Lambda - sort each row or column in range | LinkedIn and
(11) Excel, Lambda - sort range rows or columns | LinkedIn
With similar technique you may drop the spill in size. But you can't FILTER rows or columns independently, array of arrays is not supported.
Dec 12 2021 01:24 PM
Dec 12 2021 03:38 PM
Array of arrays are a major headache! The best I have come up with is to write a Lambda function that returns one volunteer from one team by index
= LAMBDA(projectNum,idx,
LET(
project, INDEX(ProjectCodes, projectNum),
team, FILTER(Projects[Volunteer], Projects[Project]=project),
volunteer, INDEX(team, idx),
IFERROR(volunteer, "")
)
)
Turning the formula into a Named Lambda function, one can then use it within MAKEARRAY to give
= MAKEARRAY(4,3,Volunteerλ)
I am told it is also possible to do this with 'thunks' (arrays placed within a Lambda function that may be expanded using an empty parameter string) but I have yet to make that work.
Dec 13 2021 03:32 AM
Actually that returns as to BYROW() as here https://techcommunity.microsoft.com/t5/excel/filter-using-criteria-from-list-array/m-p/3036525. I'm still not sure which functions are available.
Dec 13 2021 10:13 AM
I think things get a fair bit simpler once on settles for concatenation rather than 'arrays of arrays'. Strange, given that the fundamental object of spreadsheets is the 2D array, that creating such arrays should be so tricky.
My previous formula scales OK provided formulas are used to size the array. I would be much happier if I could return the list of names as an array, rather than repeating the filter and returning values one by one.
Dec 13 2021 10:59 AM
Dec 13 2021 11:38 AM
Microsoft already provides tools for both solutions - concatenated string or names as 2D array, both combined in one spill with headers (projects in this case). The only not all such tool are GA, some are still on insider (beta) phase.
Dec 13 2021 12:17 PM
With some of the necessary tools still in beta release, I do not think there is a sufficient body of practitioners to develop and refine solution approaches in order to determine best practice. At times I am just thankful to get to a solution. The most relevant example I had to your problem was having reworked the scoring of an inter-counties cross-country championship using modern methods.