6

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.

  • I wish there was a way to set up such a sort so that when I click a column and go to Data -> Sort Sheet By Column ... it would sort using my custom ordering! – Michael Sep 26 '18 at 16:14
3

You can add MATCH to your SORT for a custom order list.

    Custom Sort List with Match

The formula in D5 is,

=ArrayFormula(sort(A$2:A$99,match(left($B$2:$B$99,3),{"sop";"con";"ten";"bas"},0),1))

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.

  • I'm having trouble understanding this part in your formula: =match(left($B$2:$B$99,3),{"sop";"con";"ten";"bas"},0).. i ran this on it's own and I got this list :{1,4,2,3,4,2,3,1}.. but according to the sort docs the sort_column is: The index of the column in range or a range outside of range containing the values by which to sort.. I just can't reconcile the two.. – abbood Jul 8 '17 at 8:13
5

Make a new sheet inside your current spreadsheet. Put two columns in it:

VoiceType   Sort order
SOPRANO     1
CONTRALTO   2
TENOR       3
BASS        4

In your main worksheet (the one with the list of people), in a new column put a formula like this:

=vlookup(upper(B2),Sheet2!A$2:B$5,2,0)

Note that I have used the UPPER() function in case some of the voice types are entered as "tenor" or "Tenor.

There is a worked example showing this here: https://docs.google.com/spreadsheets/d/1s8MngBnoJ53czPOLlBJ3DRAbZHc4JZXd0KPeRBM4_tQ/edit?usp=sharing

0

Actually you don't need arrayformula() because sort() function returns rows and columns already.

In the image up there, in D5, (not E5, the blue rectangle should be at D5 in the image) you can just type:

=sort(A2:B99,match(B2:B99,{"SOPRANO","CONTRALTO","TENOR","BASS"},0),1)

That's it.

Special thanks to Jeeped. I found this while I was playing with your answer, using match() in sort() function, which I didn't know before.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.