6

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?

3 Answers 3

2

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
0

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.

3
  • 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
    Commented Jan 9, 2017 at 23:07
  • Wildcard is not the issue; it's about a long series of adjacent columns without having to specifically write each and every column in the "where" statement.
    – Tedinoz
    Commented Dec 17, 2018 at 9:48
  • You can select columns using query({A2:C,E2:F} to limit the data worked on. If you are looking for a value in the entire range I would use other functions such as a lookup, find, or match. You might want to start a new thread for your specific need.
    – Karl_S
    Commented Dec 20, 2018 at 13:01
0

Try this...

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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