Search code examples
google-sheetsgoogle-query-language

Select and Where on multiple (sequential range) of criteria

I'm looking for an SQL solution for my sheet that looks exactly 28 columns deep into a row, and if it has any non-blank values in that range, retrieves it.

Currently I'm able to get exactly what i want using this horrible formula:

QUERY(SomeOtherSheet!A85:BA,"select A,B,C,O,P,Q,R,S,T,U,V,W,X,Y,Z where (O>=0 OR P>=0 OR Q>=0 OR R>=0 OR S>=0 OR T>=0 OR U>=0 OR V>=0 OR W>=0 OR X>=0 OR Y>=0 OR Z>=0) ",1)

suffice to say the range O-Z isn't exactly 28 long, but its enough to showcase what I'm looking for in this example.

Question) Is there a better way to achieve this kind of query on a sequential range of columns? specifically in my case the range of O-AQ

I suppose i can dynamically string together in a different cell the cols required. But I'd rather not...

Solution

  • I know OP already answered. But submitting this as an alternative:

    For the Select,

      =ARRAYFORMULA(JOIN(",",CHAR(ROW(65:90)),"A"&CHAR(ROW(65:90))))

    For the where OR part:

        =ARRAYFORMULA(JOIN(">=0 OR ", CHAR(Row(65:90)),"A"&CHAR(ROW(65:90))))&">=0"

    To change the start and end parts, change the ROW number start and end parts:

    http://www.asciitable.com