Wednesday, July 3, 2013

Google Spreadsheet Filter Using Query Function


( by Ivar Gosman )


hopefully you can help me out on the following. I'm filtering out info (skipping blank cells) with the filter function in the following way:

This gives me the contents of field A2/H20 if any value is inserted in the corresponding Q cell. I would like to add column Q2:Q20 itself to the source array while skipping columns i till P. Kind of like this (but it doesnt work): =filter(Sheet3!A2:H20 + Q2:Q20;Sheet3!Q2:Q20<>"")

Right now I do this by adding the extra formula =filter(Sheet3!Q2:Q20;Sheet3!Q2:Q20<>"") in the column behind the first formula. Same result but not as nice and bulletproof as a combined formula.
In other words, i'm asking how to specifically define the source array with two fields of cells.

Thanks in advance!


Suppose we have the following data in our 'Sheet3' of the spreadsheet.

Now to get the data as per your requirement you need to use Query function instead of a Filter function.

Put the following formula in some other sheet of the same spreadsheet:

=query(Sheet3!A2:Q20;"select A,B,C,D,E,F,G,H,Q where Q<>'' ")

So, you can see the output in the above Sheet, we are fetching data from Column A to Column I and then from Column Q.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.
I also take up private and confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here:


1 comment:

  1. Hi!

    Good example, but can you give string for query where I want filter all blank cells on Q