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
    – Tedinoz
    Commented Dec 29, 2021 at 3:23

2 Answers 2


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



Item A Item B Item C Item D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.