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?

up vote 1 down vote accepted

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

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.

  • Thank for your reply. I've used the wildcard before, but I'm looking specifically for something that allows me to pick 'ranges' and exclude or skip a few columns. – Mr. B Jan 9 '17 at 23:07

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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