# Apply FILTER() to result of TRANSPOSE() with custom condition

I've got the following data in a sheet:

``````ColA    ColB ColC   ColD ... [lots of additional columns]
ItemA   A    B      C
ItemB        D      E
ItemC               F
ItemD   G           H
``````

(table A)

I am trying to apply a transpose and a filter, so that I can get something like

``````ColA ItemB
ColC D
ColD E
``````

(table B)

And another List:

``````ColA ItemC
ColD F
``````

(table C)

So far, I've got the following formula:

``````=transpose(query(Sheet!1:9,"select * where A  contains '"&\$A\$2&"'",1))
``````

To select all the columns, for a specific value of ColA, including the header and do the transpose. I would like to add a FILTER() around the transpose, so that all values with ColB, ColC, ColD...etc that are empty are skipped. Something like:

``````    =filter(transpose(query(Sheet!1:9,"select * where A  contains '"&\$A\$2&"'",1)),"WHAT DO I WRITE HERE??")
``````

However, a filter function normally requires me to reference a range in the condition (I cannot do only "<> ''" for example)

Another option would be to add "IS NOT NULL" to the QUERY function, however, the number of columns is huge (more than 200) and I may not know all of them in advance.

So, is there a way that I can extract a list like Table B and Table C from a table like Table A using a single formula?

• I am trying to apply a transpose and a filter With respect, by focusing on the method of your solution rather than your your problem, you have fallen into the XY problem Dec 29, 2021 at 3:23

I found a way to do it, but it is not the cleanest I think. I used to my favor the fact that values do not repeat in my result set:

``````=query(sort(transpose(query(Sheet!1:9,"select * where A  contains '"&\$A\$2&"'",1))),"select * where Col1 is not null")
``````

Basically, I sort and then apply a query removing any blanks

Try this formula.

`=query({INDIRECT("Sheet!A1:"&SUBSTITUTE(ADDRESS(1, COLUMNS(Sheet!1:1), 4), 1, ))}, "select * where Col1 = '"&A1&"'")`

• Create a new sheet
• Enter the required Item name in cell A1 (such as `Item A`, `Item B`, `Item C`, etc)
• Enter the formula in Cell A2

Managing "Lots of Columns"

The formula will manage an infinite number of columns.
HT: @player0 in StackOverflow Google Sheets Query sum indeterminate number of columns

Sample data Output