How to sort groups of rows in Google Sheets?

Question

I have a spreadsheet with lots of related rows. How can I sort the entire sheet in such a way that rows that belong together stay together in groups, and the groups instead are sorted by some common property?

For example, this sheet with 6 groups and 2 columns

set1    element1     1
set1    element2     33
set1    element3     ?
set1    element4     5
set2    element5     1
set3    element6     6
set4    element7     14
set4    element8     14
set5    element9     3
set6    element10    1
set6    element11    8
set6    element12    13

Some criteria I'd like to sort by:

• Order groups so ones that have at least one '?' in column C are on top,
• Order groups by the minimum/maximum/average/etc value of all cells in column C of the group that are not '?'

The relative order of rows inside of groups should not be changed.

I realize this will require some scripting, but I've only done 1-line scripts before and even those were found here, so I can't do this by myself. With regards to the processing time, the sheet is about 11270x20 cells and two columns are filled with formulas like the following:

=if(isdate(F4),if(F4="?","?",(year($F4)-2000)*4+floor(($F4-date(year($F4),3,1)+if(month($F4)<3,60,59)+13.3)/91.8,1)),"…")
=if(and(E3="",H3=""),"…",if(isnumber(H3*E3),H3*E3,"?"))

Answer 1

Short answer

The desired result could be obtained without scripting and this is recommended for cases where number of cells is small, especially if the OP don't know about scripting. To do so,

1. Add auxiliary columns for each sorting criteria
2. Then use the SORT() or QUERY() functions.

Extended answer

Google Sheets has some additional functions regarding those available in other spreadsheet applications. Two of them are SORT() and QUERY() which allow to get the desired result without scripting.

As QUERY() return a blank cell for those containing ? as value, the SORT() function will be used.

Original data

Assume that the original data is in A2:D13.

Auxiliary columns

Auxiliary columns will be used for sorting. Only one aggregate function is presented in order to keep the answer short.

Add the following formulas and fill down until the last row of the source data.

D2: =Row(C2)-Row($C$2)+1
E2: =ISERROR(LOOKUP("?",OFFSET($A$2,MATCH(A2,$A$2:$A$13,0)-1,2,COUNTIF($A$2:$A$13,A2))))
F2: =AVERAGEIF($A$2:$A$13,A2,$C$2:$C$13)

If you find helpful having column headers, add them to the row 1.

Key Formula

G2: =ARRAY_CONSTRAIN(SORT(A2:F13,5,TRUE(),6,TRUE(),1,TRUE()),12,3)

ARRAY_CONSTRAIN is included to limit the result to the columns of the source data.