1

I have this formula:

=QUERY(
    {AG4:AW101},
    "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17 
    where Col1 is not null
    and Col1 is not null
    and Col2 is not null
    and Col3 is not null
    and Col4 is not null
    and Col5 is not null
    and Col6 is not null
    and Col7 is not null
    and Col8 is not null
    and Col9 is not null
    and Col10 is not null
    and Col11 is not null
    and Col12 is not null
    and Col13 is not null
    and Col14 is not null
    and Col15 is not null
    and Col16 is not null
    and Col17 is not null
    order by Col"&$N$1&" "&$N$2&"")

Is there anyway to make it shorter? Do I really need to repeat and ColX is not null for every column?

player0
  • 124,011
  • 12
  • 67
  • 124
Verminous
  • 490
  • 3
  • 14
  • 1
    Why would you want to do that? The query looks very readable. You can get it shorter by removing `and Col1 is not null` :-D And you could concatenate the columns or add them up if they are numeric and then check the result for null, but this would make the query less readable and may also slow it down. – Thorsten Kettner Jul 06 '22 at 21:43
  • You mean like player0 wrote down below? You think his solution is less readable? Or were you thinking of something else? – Verminous Jul 06 '22 at 21:47
  • 1
    I was thinking along the lines of `where col1 + col2 + ... is not null`. I consider this less readable, and as mentioned it could slow down the query immensely. And yes, player0's answer may be clever, but I consider the query very bad in terms of readability (and thus maintainability). – Thorsten Kettner Jul 06 '22 at 21:53
  • 1
    Do you consider their query readable? Would you know at a quick glance what it's about? It is very easy with your original query, and I doubt you'll find any as readable as yours. – Thorsten Kettner Jul 06 '22 at 21:55
  • You're right about readibility. But I am going to try to understand how he did it and thus will become readable I think. – Verminous Jul 06 '22 at 22:12

1 Answers1

1

join them. lets say your range is A:F and you want to check B:F for is not null, then try:

=ARRAYFORMULA(QUERY({A:F, TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9)))}, 
 "select Col1,Col2,Col3,Col4,Col5,Col6
  where Col7 is not null", ))
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    I don't know how that works but it works even better than mine because for some reason my formula was hiding some rows and yours is not. Would you mind explaining a bit more the logic behind this? – Verminous Jul 06 '22 at 22:13
  • 1
    @Verminous A:F equals to Col1,Col2,Col3,Col4,Col5,Col6. then Col7 is made by joining columns B:F into one single column which is achieved by TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9))). if you want to run it separately to see what's going on try: `=INDEX(TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9))))` more on this query smash can be found here: https://stackoverflow.com/a/65435321/5632629 – player0 Jul 06 '22 at 22:19