google sheetsgoogle-sheets-query
I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)?
Is there in the Google Query Language something similar to FIELD() in MySQL?
I also tried the SORT function, but it does not seem to have an option for custom ordering, either.
Both your syntax is wrong. Both shouldn't be working and they're not working. You just think that the first one is working.
ORDER by F will not and does NOT cause the parse error.
There should be no , after C1
If C1 is number,
=QUERY(data!A2:Z, "select G, E, F, I, J WHERE A = "&C1&" ORDER by F")
If C1 is text,
=QUERY(data!A2:Z, "select G, E, F, I, J WHERE A = '"&C1& "' ORDER by F")
OR
=QUERY(data!A2:Z, "select G, E, F, I, J WHERE A contains ' "&C1& " ' ORDER by F")
Notice the position of &,' and " in different instances.
Try the function COUNTA, it counts the number of cells in a range that are not empty,
and indicate more than zero for conditional rule:
Was this what you were looking for?
Best Answer
You can add MATCH to your SORT for a custom order list.
The formula in D5 is,
Copy to E5. Without speed testing the calculation cycles it's debatable whether using the Left function to peel off the first three characters is seriously detrimental but it certainly makes creating the constant array for the Match range lookup easier to create and maintain. Be careful that you do not duplicate the first three characters if you expand the list.