Filter an array row-by-row, or sort row-by-row

Brass Contributor

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.) 

 

9 Replies

@boukasa 

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))

@boukasa 

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.

Juliano-Petrukio, the IF approach creates a dynamic array that does not require copy-down maintenance. I am looking for a solution that continues to work automatically when new projects and volunteers are added.

@boukasa 

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λ)

image.png

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.

@Peter Bartholomew 

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.

@Sergei Baklan 

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.

image.png

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.

@boukasa 

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.

@boukasa 

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.