Google Sheets Query to select a large range of columns without manually entering each column

Question

I have a column with dozens and dozens of columns in it that I'm querying data from. I'm using something similar to below to do this, but I'm wondering if there is an easier way? For instance, something like "Select B:Z where K = X" or "Select B through Z where K = X" or "Select * excluding S where K = X"?

=QUERY(data!B1:J30000, "Select B, C, D, E, F,.......

Is it possible to select columns based on a 'range' instead of manually entering each column?

Answer 1

You can generate a comma-separated string of column names with a formula such as

=arrayformula(join(",", substitute(address(1, column(C:F), 4), "1", "")))

(here C:F is the example of a range of columns). The above formula returns the string C,D,E,F which is suitable for use in a query. Optionally, the arrayformula part can go outside. Example:

=arrayformula(query(A1:F, "select " & join(",", substitute(address(1, column(C:F), 4), "1", "")) & " where A <> ''"))

For a short range like C:F this obviously isn't worth the effort, but if you have something like M:AT, then it's probably going to save time.

Explanation

• column returns a column number
• address returns A1 notation of the cell at the top of that column (option 4 means no $ will be used)
• substitute removes row number 1
• join joins by commas

Answer 2

As long as your returned columns are continuous and include the item you are checking on, you can use the select *:

=QUERY(data!B1:J, "Select * where K = 5")

Or some other Where clause that returns items other than blank rows.

If your columns are not continuous, or you want to change the order, I have not seen a method which allows for defining a range. You would need to list each column.

Answer 3

Try this...

=QUERY(TRANSPOSE(QUERY('Data'!A1:AH,"SELECT * ",1)),"SELECT * WHERE Col1 <> 'Column name'",1)