Wednesday, August 17, 2011

yogi_Query A Range To Meet Certain Criteria Where A Column Has Mixed Text And Numeric Data

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

aussie scott said ...
I have the following questions in relation to the following document.
FS! is a sheet where a form "lands" as people clock on and off. Because I can only have 1 form per doc I ask the people to fill in a start time on starting their work and in the finished time question the default is 'At Work', when completing their shift they fill in the start time again and a finished time. Therefore you can see when people are available 'At Work' and use the completion form to calculate there hours worked. I was then expecting to be able to set up a individuals record sheet to track there hours worked and the book keeper refer to this in calculating their payment. I would like the result to be as in sheet "BS result I want" - all of a persons entries except their shift start entries. When I write a query formula as in "BS where" I get all of their entries - as expected. However when I write the formula with a second parameter as is "BS where and" I don't then get the result I expect. Frustratingly I don't know why or how to amend the formula. Could you amend =QUERY(FS!A1:H ; "Select B,F,E,D,C,G,H where B = 'Bob Smith' and H != 'At Work' Order by F,E,D,G" ; 1) so I get the "BS result I want".
Secondly is the a method of using cell A1 in each sheet and formula instead of the name ('Bob Smith') in each formula?
Many thanks for taking the time to help.
-------------------------------------------------------------

In sheet Resul01, since Column H in the sheet FS is of mixed data type ... so in the process of QUERYing I converted the range of interest A:H to TEXT using FS!A:H&"" ... so all the result of the QUERY are TEXT elements ... but that should not be a problem because the expected numerics are easily coerced into true numerics if so desired.

In sheet Result02, I used both QUERY and FILTER as the primary functions