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

Question

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

peterbartholomew1 · Answer

boukasa&nbsp;Array of arrays are a major headache!&nbsp; 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.

peterbartholomew1 · Answer

SergeiBaklan&nbsp;I think things get a fair bit simpler once on settles for concatenation rather than 'arrays of arrays'.&nbsp; 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.&nbsp; 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.

juliano-petrukio · Answer

boukasa&nbsp;Why do you need IF() statement and then filter each row with FALSE value?Using filter you can achieve it easily.&nbsp;=TRANSPOSE(
    FILTER(Projects[Volunteer],Projects[Project]=H15))

sergeibaklan · Answer

boukasa&nbsp;
You may sort entire 2D spill row by row or column by column. Samples are&nbsp;
(11) Excel, Lambda - sort each row or column in range | LinkedIn&nbsp;and
(11) Excel, Lambda - sort range rows or columns | LinkedIn
&nbsp;
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.

boukasa · Answer

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.

Forum Discussion

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

9 Replies

Resources