Friday, July 26, 2013

yogi_WorkAround For Querying Data When Source Data Has Field(s) With Mixed Data (Numeric and Text)

                                          Google Spreadsheet   Post  #1308
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 26, 2013
user Jared Hamilton (http://productforums.google.com/forum/?zx=hmnbrbxx6547#!category-topic/docs/spreadsheets/m8CsEJyIHKE)
Spreadsheet Query problem
I was creating a spreadsheet to save our school dismissal person some time which is located here.  All data should be entered in "All Dismissal" then feeds out to appropriate tabs by Query.  My equation on the second sheet "3:15 Bus" is =QUERY('All Dismissal'!A2:I264,"Select B,C,D,E,F where I = '3:15 Bus' order by F")

My problem is that the query being made from the "3:15 Bus" sheet is only pulling the F column from the "All Dismissal" sheet when it is a number.  If anything other than a number is put in the F Column of "All Dismissal" then that column comes up blank in the "3:15 Bus" sheet.  I highlighted 2 examples in the spreadsheet.
---------------------------------------------------------------------------------------------------------------------------------------------
The problem is that column F in the source data (sheet named All Dismissal) has mixed data (Numeric and Text) ... QUERY function expects a field to have the same data type in its entirety ... so I used a WorkAround as presented in the following