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                  

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