4

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,"?"))

migrated from superuser.com Dec 29 '15 at 19:03

This question came from our site for computer enthusiasts and power users.

2

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.

  • 1
    Sadly auxiliary columns are not an option. The sheet is huge and already very slow. – Zom-B Dec 31 '15 at 14:36
  • @Zom-B: Due to time execution processign limit for scripts you should mention in the question.how big is your spredsheet – Rubén Dec 31 '15 at 15:34
  • Now that Sheets provides the ability to group rows, I wonder if this answer can be updated to reflect some mechanism whereby the group rows each follow their header row around in a sort. – Michael Jul 17 '18 at 18:38
  • @Michael Since the nature of the groups on the question and the new row groups are different I suggest to post a new question. – Rubén Jul 17 '18 at 18:58
  • Thanks, I have done that – Michael Jul 17 '18 at 19:02

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.