Possible but may require some 'compromising':
=arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1))
in D1 to F10 does find the 2 (which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value, though this could be added back later if required.
in say G1 and copied down to suit:
=arrayformula(LARGE(value(D$1:F$10),row()))
then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!):
=unique(G:G)
The results will be in reverse order from as shown by you (sort?) because of use of LARGE rather than SMALL because if positive values only all the 0 would have come first and there are quite a few of those.
Concatenate results with ="Value "&H1 etc if required.
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
Best Answer
This is what is shown in the linked spreadsheet
Please note that each column includes a dropdown button. That is the way that Filter/Filter views work on Google Sheets.
If you want that the filter shows all the values in the array, you should change the shape of the array from 3 columns 1 row to 1 column 3 rows as follows
If you want to keep the original array shape, then you will have to use another method to filter your array, like using functions (i.e. QUERY, FILTER)