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

Question

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?

Answer 1

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

Answer 2

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