Forum Discussion
Filter an array row-by-row, or sort row-by-row
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.
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.
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.
- Peter, it did cross my mind to use the concatenation and then a formula to break it back into its parts, but then I realized this will probably put me right back into an array of arrays and won't work. I hope MS will move down this road in the future - my example seems like an obvious use case.
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.
