CODEWITHSUNDEEP
google-sheetsgoogle-query-language
HazirBot
HazirBot

Reputation: 323

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...

Upvotes: 0

Views: 616

Answers (3)

Max Makhrov
Max Makhrov

Reputation: 18707

Please try converting the range into array:

QUERY({SomeOtherSheet!A85:BA},"select Col1,Col2,Col3,...")

I always use this method and search for columns inside query by their index, e.g. look for column called "Name":

=match("Name", SomeOtherSheet!A85:BA85, 0)

Update. To combine OR part of query:

="where Col"&JOIN(" >= 0 or Col",ARRAYFORMULA(COLUMN(O1:Z1)))&" >= 0"

The result is:

"where Col15 >= 0 or Col16 >= 0 or Col17 >= 0 or Col18 >= 0 or Col19 >= 0 or Col20 >= 0 or Col21 >= 0 or Col22 >= 0 or Col23 >= 0 or Col24 >= 0 or Col25 >= 0 or Col26 >= 0"

If new columns will be pasted inside range O1:Z1 this formula will adjust. In real projects it's useful to find ranges by their points: start cell, last cell and then use indirect:

="where Col"&JOIN(" >= 0 or Col",ARRAYFORMULA(COLUMN(indirect(range))))&" >= 0"

where range is name of range with string "O1:Z1", "O1" and "Z1" are found by their column names or ids.

Upvotes: 1

TheMaster
TheMaster

Reputation: 50462

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

Upvotes: 2

HazirBot
HazirBot

Reputation: 323

Well I've worked out how to dynamically concatenate a string with as few possible static magic numbers:

First, A cell that contains:

=ARRAY_CONSTRAIN(ArrayFormula((IF((COLUMN(Lifetime!N$8:$8)+64)>CODE("Z"),"A"&CHAR(MOD(COLUMN(Lifetime!N$8:$8),26)+64),""&CHAR(COLUMN(Lifetime!N$8:$8)+64)))),1,28)

which gives us the following array:

N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO

this array can be used for the Select part of the query easily. I've allowed myself the freedom of not accounting for cells that will be B* since my personal sheet won't get there. The Array_constrain() column parameter allows for quick change to different size of arrays if ever needed in the future.

Secondly, a simple textjoin() + regexreplace() for the required where clause

=REGEXREPLACE(TEXTJOIN(",",FALSE,A119:AB119),",",">=0 OR ")&">=0"

which results in the required string for the where clause:

N>=0 OR 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 OR AA>=0 OR AB>=0 OR AC>=0 OR AD>=0 OR AE>=0 OR AF>=0 OR AG>=0 OR AH>=0 OR AI>=0 OR AJ>=0 OR AK>=0 OR AL>=0 OR AM>=0 OR AN>=0 OR AO>=0

And the final product:

=ARRAY_CONSTRAIN(QUERY(Lifetime!A85:BA,"select A,B,C,"&120&" where ("&A121&")",1),105,20)

or as a one-liner:

=REGEXREPLACE(TEXTJOIN(",",FALSE,ARRAY_CONSTRAIN(ArrayFormula((IF((COLUMN(Lifetime!N$8:$8)+64)>CODE("Z"),"A"&CHAR(MOD(COLUMN(Lifetime!N$8:$8),26)+64),""&CHAR(COLUMN(Lifetime!N$8:$8)+64)))),1,28)),",",">=0 OR ")&">=0"

Upvotes: 0

Related Questions