Thursday, January 5, 2012

yogi_Format A Column As Specified In A Retrieved Filtered List

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user FunSpiritMan said:
I have a filtered list of Numbers, Text fields, and Dates. I'd like to view the filtered data with the Dates formatted as "Thu 05-01-2012" for example.
My problem is I have a double filter: 1) to retrieve based on SEARCHes across many columns; then 2) a 2nd filter used with an embedded array ({0,1,1,1,0}, for example) to only display the columns of interest.
See a sample representation of my spreadsheet data below. 
002 abc 1/5/2012 t xyz
003 abd 1/6/2012 n xyz
004 abe 1/7/2012 o xxx
005 abf 1/6/2012 t xyz
006 abg 1/8/2012 w xyz
Filter 1:  Col 4="t", Col 5="xyz"
Filter 2: {0,1,1,1,0}
Filtered result should look like:

abc  Thu 05-01-2012      t
abf  Fri 06-01-2012      t

I thought about using QUERY but my actual data is much more complicated with many searches and date selections. Can anyone show how this can be done using FILTERs?
-----------------------------------------------
following is my convoluted solution to the problem: