I am trying to figure out a formula to show all the projects for which there are hours assigned. From a first query I get the following output, listing projects and assigned hours:

Now I want to filter for projects with assigned hours. Since I want to add this to my existing query, I cannot use A1 notation. Any idea? :)

CodePudding user response：

Try

=unique(query(arrayformula(split(flatten((A1:C1&"~"&A2:C)),"~")),"select Col1 where Col2 is not null"))

CodePudding user response：

transpose({transpose({A1:C1}),query(transpose(query({A2:C},"select "&textjoin(",",1,index("sum(Col"&sequence(1, 3,1)&")"))&"), "select Col1 where Col2 >0")})

I solved the issue with this formula. But yours also worked @Mike Steelson -thanks!

CodePudding user response：

Here's another way you can do that:

=flatten(filter(A1:C1,trim(query(A2:C,,9^9))<>""))