Friday, February 4, 2011

yogi_Querying Fields With Mixed Data Type -- Be it Numeric Text or Blank


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com


Querying data from fields with mixed type data
can become a daunting task -- especially in regard to
what is considered null in dealing with different data types



formula for extracting rows where Col2 is Blank:
=query(index(SourceData!A:C&""),"select* where Col2 ='' ",1)


formula for extracting rows where Col2 is NotBlank:
=query(index(SourceData!A:C&""),"select* where Col2 <>'' ",1)


formula (in row2 -- start of data) for extracting rows where Col2 has NumericData:
=filter(query(SourceData!A2:C,"select* where B!='' ",0),isnumber(index(query(SourceData!A2:C,"select* where B!='' ",0),0,2)+0))

formula for extracting rows where Col2 has TextData:
=query(index(if(isnumber(SourceData!A:C),"",SourceData!A:C)),"select* where Col2<>'' ",1)