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
I am trying to apply a transpose and a filter, so that I can get something like
ColA ItemB ColC D ColD E
And another List:
ColA ItemC ColD F
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?