yogi_Query Data In Another Sheet For Different Criteria And Sort results By Different Columns

                  Google Spreadsheet   Post  #1784
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-10-2014
post by  s415tandon:
(https://productforums.google.com/forum/#!mydiscussions/docs/u4y7vbLglUE)
Query, Sort By is sorting as a string even though it only contains numbers

I'm so very confused. I built the worksheet tab by section so I could troubleshoot as I added.

Before I added the where "&A7&" is not null part, sort by worked just fine. Everything was a number. I added the not null part to remove any extra rows that didn't pertain to the user entered data. The first time I put "&A7& != ''  which did not filter out the rows with an empty value in a particular column. I read that because it's a number, I should be using is not null.

My problem is now that only the first two columns sort correctly, the rest of the columns sort as if the numbers were strings. For example sorting by desc: 98, 8, 72, 664, 5, 4, 32.7. I tried pasting just the data as values into a new sheet and referencing that, it made no changes. I saw there is a format clause, but I cannot use it since I am using where. What I don't understand as well is that all except the header is a number.

Here is an example of the formula I use:

=IF(C5="All",QUERY(Data!A2:AR1844,"select A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ where C <= "&C3&" order by "&A7&" desc ",-1),QUERY(Data!A2:AR1844,"select A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ where '"&C5&"' contains A and C <= "&C3&" order by "&A7&" desc",-1))

Thanks in advance!

---

Oops, sorry I posted an older formula, I had taken the null part off to see if it was making the column a string but nothing really changed when I took it off.

Here is a copy of the spreadsheet, the original contains a lot of data.  One odd thing I noticed is that some columns are locked as numbers and others locked as strings. They all contain numbers, but when I select all and change the format to have leading 0's (for strings) the columns that are numbers stay the same. When I select all the columns and change those with numbers to show 2 decimal points, the problem columns that are strings will not change.

http://goo.gl/XhK281

I thinned out the data to make it a bit speedier.  Essentially there are categories and items within the categories.  Each item has 6 levels. What I'm trying to do is to get 2 to 3 user inputted values, such as what they're looking for and another qualifier, and optionally the category they are looking for and then show them the best items in descending order.  Because the actual spreadsheet has 1844 rows, I wanted to remove the excess data that does not pertain to the inputted values. 

Here is the formula I have been using:

=IF(C5="All",QUERY(Data!A2:AR1844,"select A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ where "&A7&" is not null and C <= "&C3&" order by "&A7&" desc",-1),QUERY(Data!A2:AR1844,"select A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ where is not null and '"&C5&"' contains A and C <= "&C3&" order by "&A7&" desc",-1))

To break it down the user inputs 3 values. Each input box has data validation so the expected result doesn't throw everything off.

• Cell C3 - which corresponds directly with column C
• Cell C4 - Which corresponds with one of the row titles, to find which I have a separate worksheet that has the values of the titles and their column letter, it inputs the value of this column into a cell (A7) *This is the most important piece of data, the rest generally round down the data
• Cell C5 - Optional category which corresponds with column A.

IF(C5="All"

True: If user inputs all categories, we have no need to filter column A so we skip that

Query the data worksheet and populate all the columns from the worksheet.

where "&A7&" is not null

Skip any rows that have no numerical value in the selected column so the spreadsheet is not 1844 rows long

order by "&A7&" desc"

 Arrange the data in descending order of the cells in the column specified by the user inputted data for C4 (vlookup to translate that to a column, which is put in letter format in cell A7)

False: Query the data worksheet and populate all the columns from the worksheet.

where "&A7&" is not null

Skip any rows that have no numerical value in the selected column so the spreadsheet is not 1844 rows long

'"&C5&"' contains A

We'll add another part to the query that the column A also has to match cell C5

order by "&A7&" desc"

 Arrange the data in descending order of the cells in the column specified by the user inputted data for C4 (vlookup to translate that to a column, which is put in letter format in cell A7)

It would be nice to only show columns that have values, but I think that would take a lot more work. Hopefully this makes sense... Sometimes they make sense to only me :)  Thank you! 

-----------------------------------------------------------------------------------------------------------------------------------------------