Google Spreadsheet Filtering Data on Dates Option2

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.

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

I made a mistake in the example I sent to your guys...

In the first example, I only had 1 comment per entry. Realistically, I will have multiple comments per entry. I edited the sheet:

and highlighted the column and the results.

Thanks to the both of you for your replies. If this is too hassle much then please ignore!

Thanks again for your efforts!

Williams

Solution:


You will need to use custom function "VMerge", to get your desired results. You can download "VMerge" from Script Gallery.

Go to menu "Tools" > "Script Gallery" search for "VMerge" and then install it.

After installing it follow the instructions below:

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(Vmerge(query('Sheet1'!A2:G;"select A,B,C,D");query('Sheet1'!A2:G;"select A,B,C,E");query('Sheet1'!A2:G;"select A,B,C,F");query('Sheet1'!A2:G;"select A,B,C,G"));"select Col3,Col4 where Col2='Location 1' and Col4<>'' and toDate(Col1) >= date '"&text(B1;"yyyy-MM-dd")&"' and toDate(Col1) <= date '"&text(B2;"yyyy-MM-dd")&"' order by Col3 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 of 'Sheet2':

I have the following formula in Cell A5:

=query(Vmerge(query('Sheet1'!A2:G;"select A,B,C,D");query('Sheet1'!A2:G;"select A,B,C,E");query('Sheet1'!A2:G;"select A,B,C,F");query('Sheet1'!A2:G;"select A,B,C,G"));"select Col3,Col4 where Col2='"&B3&"' and Col4<>'' and toDate(Col1) >= date '"&text(B1;"yyyy-MM-dd")&"' and toDate(Col1) <= date '"&text(B2;"yyyy-MM-dd")&"' order by Col3 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:

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