Thursday, January 5, 2012

yogi_Format A Column As Specified In A Retrieved Filtered List

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
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: