Google Spreadsheet Filter Using Query Function

Question:

( by Ivar Gosman )

Hi,

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:

=filter(Sheet3!A2:H20;Sheet3!Q2:Q20<>"")

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!

Solution:

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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive