Thursday, August 8, 2013

Google Spreadsheet Filtering Data On Dates and Location

Question:

( by nerfsmurf )



My Google form has multiple comment sections after every section so on the spreadsheet I have multiple comment columns. And I would also like to filter which comments are displayed based upon date.

Enough with the explanation, here is the same example, just a bit modified.



Solution:


Have a look at the following screenshot of Sheet "Sheet1" of my Spreadsheet:



Have a look at the following screenshot of Sheet "Location 1" of my Spreadsheet:


I have the following formula in Cell A4:

=query(arrayformula(if({1,1,1,0};'Sheet1'!A2:C;if('Sheet1'!D2:D<>"";'Sheet1'!D2:D;if('Sheet1'!E2:E<>"";'Sheet1'!E2:E;if('Sheet1'!F2:F<>"";'Sheet1'!F2:F;if('Sheet1'!G2:G<>"";'Sheet1'!G2:G;""))))));"select Col3,Col4 where Col2='Location 1' and Col3<>'' and toDate(Col1) >= date '"&text(B1;"yyyy-MM-dd")&"' and toDate(Col1) <= date '"&text(B2;"yyyy-MM-dd")&"' label Col3 'Sub Location', Col4 'Comments' ")

Or you can also have the following formula in Cell A4:
=query(arrayformula(if({1,1,1,0};'Sheet1'!A2:C;('Sheet1'!D2:D)&('Sheet1'!E2:E)&('Sheet1'!F2:F)&('Sheet1'!G2:G)));"select Col3,Col4 where Col2='Location 1' and Col3<>'' and toDate(Col1) >= date '"&text(B1;"yyyy-MM-dd")&"' and toDate(Col1) <= date '"&text(B2;"yyyy-MM-dd")&"' label Col3 'Sub Location', Col4 'Comments' ")


And you can change the location Location 1 in the formula to get for another location.

==================

And you can also add "Location" on the Sheet2 itself, have a look at the following screenshot:



I have the following formula in Cell A5:

=query(arrayformula(if({1,1,1,0};'Sheet1'!A2:C;if('Sheet1'!D2:D<>"";'Sheet1'!D2:D;if('Sheet1'!E2:E<>"";'Sheet1'!E2:E;if('Sheet1'!F2:F<>"";'Sheet1'!F2:F;if('Sheet1'!G2:G<>"";'Sheet1'!G2:G;""))))));"select Col3,Col4 where Col2='"&B3&"' and Col3<>'' and toDate(Col1) >= date '"&text(B1;"yyyy-MM-dd")&"' and toDate(Col1) <= date '"&text(B2;"yyyy-MM-dd")&"' label Col3 'Sub Location', Col4 'Comments' ")

Or you can also have the following formula in Cell A5:

=query(arrayformula(if({1,1,1,0};'Sheet1'!A2:C;('Sheet1'!D2:D)&('Sheet1'!E2:E)&('Sheet1'!F2:F)&('Sheet1'!G2:G)));"select Col3,Col4 where Col2='"&B3&"' and Col3<>'' and toDate(Col1) >= date '"&text(B1;"yyyy-MM-dd")&"' and toDate(Col1) <= date '"&text(B2;"yyyy-MM-dd")&"' label Col3 'Sub Location', Col4 'Comments' ")


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

Thanks,

No comments:

Post a Comment