How do I filter the array returned by a filter or other formula?

Question

I want to filter an array returned by a filter to remove blank columns. Here is a picture of an example sheet:

Here is the example sheet.

I am filtering the large table by the rows that are of type b. I am retrieving the columns via the key on the left. This is a very basic example of my sheet, where there may be many rows of type b, but there are only two columns of type b. The column headers may be the same as other headers, and they may change based on the key.

I understand how to perform a two-dimensional filter by putting the results of one filter inside of another. However, I cannot figure out how to filter the result of a filter by itself.

If I have a formula that returns 5 columns and 3 rows. How do I say: "I want to filter out all columns that are blank". This would normally be something like Filter(A1:E5, A:E <> "") where A1:E5does not actually exist on the spreadsheet, but is instead an array in a formula (Not sure what that is technically called).

Hopefully this is better explained than my last question, if it is not, please let me know.

Answer 1

Short answer

Assuming that each set of columns identified by its type will not have blank cells, a double QUERY and TRANSPOSE could be used to filter them:

=Transpose(
     QUERY(
         Transpose(
              QUERY(
                  E1:M13,
                  "Select * where M = '"&F15&"'",
                  1
              )
         ),
         "Select * Where Col2 <>''"
     )
 )

Explanation

Google Sheets doesn't include functions able to "filter columns" as was called by the OP, but there are several ways to achieve the desired result. In this answer, one of this ways is presented.

From the deepest function in the formula to de shallowest:

QUERY(E1:M13,"Select * where M = '"&F15&"'",1)

Returns all the columns where the type set in column M match the value set in the cell F15.

First TRANSPOSE() occurrence

Change columns to rows.

The result until this point will be referred as X.

QUERY( X , "Select * Where Col2 <>''")

Filters the original columns and returns those without any value. Col1 correspond to the table headers, Col2 correspond to the first row of rows that match the filtering criteria of the first QUERY().

Second TRANSPOSE() occurrence Chang columns to rows so the result shape correspond to the shape of the original data.

Remarks

In Google Sheets,

• An array is a two dimensional sets of values that could be denoted by enclosing them between braces { , }.
• A range is also two dimensional set of cells that could be denoted by cell references, i.e. A1, A1:E5.
• A result of a function or formula could be a single or multiple values. Technically it could be said that a result is a two dimensional array from 1 X 1 to n X m

References

• Using arrays in Google Sheets - Google Editors Help