Forum Discussion
Filter an array row-by-row, or sort row-by-row
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.
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.
