google-sheetsselectgoogle-sheets-formula

Select from series of columns without blanks and then where Google Sheets query

I have a Google Sheet with 18 columns that is mostly populated from a Google Form. To simplify the form, I have used "Go to" and Form Ranger to simplify the options in the form. That results in columns EFG being "8th Grade Name, Class Leaving, and Going To" while columns HIJ are "7th Grade Name, Class Leaving, and Going To" an KLM are 6th Grade name, Class Leaving, and Going To." I want to pull a report using a query formula but am not sure of the proper syntax or the proper question to ask.

Here is an example of what I have:

Timestamp Email Status Grade EEE FFF GGG HHH III JJJ KKK LLL MMM
Cell 1 Cell 2 In data. X X X
Cell 1 Cell 2 Out data. R R R
Cell 1 Cell 2 Out data. F F F
Cell 1 Cell 2 In data. G G G
Cell 1 Cell 2 Out data. V V V

On a different sheet, what I want to do is this: =query(Table!$A:$M, "select A,@,@,@ where C='Out' order by A asc, @ asc)

The @,@,@ would be EFG or HIJ or KLM depending on which are not blank (null) and the last @ would be the name field used (either E, H, or K).

I want the result to look like this:

Timestamp Email Status Grade Name Class Location
Cell 1 Cell 2 Out data. R R R
Cell 1 Cell 2 Out data. F F F
Cell 1 Cell 2 Out data. V V V

How do I get the select within a select? I thought about a "select *" but I don't want all of the columns reported back out. My guess is that it includes a FROM but I'm not getting the syntax.

Solution

  • Here's one approach you may adapt/test out:

    =query({A2:D,byrow(E2:M,lambda(Σ,torow(Σ,1)))},"Where Col3='Out'")

    enter image description here