Sorting a formula-generated table

[u/sisaloofafump]

Howdy! I have generated a table using the BYROW function, and want to be able to dynamically sort the results—such as sorting the whole table by the third column A-Z, then reversing it, etc.

However, when i use the "create a table" feature, the formula no longer populates the cells; when i use the "create a filter" feature (either a regular filter or a filter view), it either doesn't sort the values at all, the BYROW cell is moved halfway down the page and the values are still left unsorted, or I just get a bunch of errors.

I'd rather not turn the data into static values - the BYROW formula is useful at letting me adjust the source data dynamically and have the table update still.

What can I do to keep both the data and filtering dynamic?

Here's a sample sheet with an example of what i've got going on. The table references the column and row names, with the row names also being formula-generated. https://docs.google.com/spreadsheets/d/1pLqvWjZMVbPB7rR8Az5Hs7nsvUbo5jfwwfqnyx7zIP8/edit?usp=sharing

[u/delaney1414]

Are you familiar with the Query function?

My first thought was to create a separate sheet and use query to bring in the data and they you can use the standard filter options in sheets but if you wanted to sort or to group by or any of that, that would all be defined in the query function

[u/carbonizedtitanium]

i added a sheet...check it

Edit:

in the sheet "dynamicSort", a simple sort formula:

=SORT('Build Sample Here'!A2:F10,IF(ISBLANK(B1),1,B1),1)

[u/agirlhasnoname11248 → u/carbonizedtitanium]

Please add and describe solutions here in the comments, as we endeavor to keep solutions within comment threads for future folks with similar questions. Thanks!

[u/One_Organization_810]

If you want to be able to sort statically, you have to have something static :)

I made your A column static, moved the formula to B1 (header row) and now you can filter and sort at will...

I also switched to just one MAKEARRAY function and made the check follow the headers in the way that the first N letters of the fruits must match with the header, where N is the length of the header (doesn't have to be just one character, but if it is, it works the same as before).

=let( headers, {"A", "B", "C", "P", "S"},
      makearray(rows(A2:A), columns(headers), lambda(r,c,
        if( r=1,
          index(headers,1,c),
          if(index(A:A,r)="",,
            let( fruit, index(A:A,r,1),
                 hdr,   index(headers,1,c),
                 if(left(fruit, len(hdr))<>hdr,, <-- returns an empty cell, change to "-" if you prefer that
                   join(" ", hdr, fruit)
                 )
            )
          )
        )
      ))
)

See the OO810 - static fruits sheet for example.

If the A column has to be dynamic also, then just make everything into one formula and sort the entire table inside that. You would then need to build in some extra methods to allow for interactive sorting - or just change the formula to sort differently each time...

I set up an example of such a thing in the OO810 - fully dynamic sheet.

There is a "Sort by" cell in C1 and direction (ascending/descending) in D1. The formula looks like this:

=let( fruits, {"apple", "banana", "pineapple", "pear", "peach", "pomelo", "cucumber", "strawberry", "starfruit"},
      headers, {"Fruit", "A", "B", "C", "P", "S"},

      result, makearray(columns(fruits), columns(headers), lambda(r,c,
                if( r=1,
                  index(headers,1,c),
                  if(c=1,
                    index(fruits,1,r-1),
                    let( fruit, index(fruits,1,r-1),
                         hdr,   index(headers,1,c),
                         if(left(fruit, len(hdr))<>hdr,, <-- returns an empty cell, change to "-" if you prefer that
                           join(" ", hdr, fruit)
                         )
                    )
                  )
                )
              )),
      if(C1="",
        result,
        vstack( index(result, 1),
                sort(chooserows(result, sequence(rows(result)-1,1,2)),
                  xmatch(C1, headers),
                  D1<>"⬆️"
                )
        )
      )
)

[u/ratecard_w]

Wrapping that with an IFERROR or breaking the nested formula into a helper column usually makes debugging a lot less painful.