2

I've got a large sheet (tens of thousands rows) grouped into hundreds of different groups. All the groups are collapsed so I only see the top-level cell of each group. Now I want to sort on a these top-level cells. But when I do so, the collapsed groups don't move with the cells.

For a simple example, if I have the following sheet with 6 rows and 2 columns:

A
    A1
    A2
B
    B1
    B2

Select rows 2 and 3 and under the "Data" menu select "Group rows 2-3", then select 5 and 6 and "Group rows 5-6", then click on the "-" for each group to collapse it. Now "Data" -> "Sort sheet by Column A, Z -> A".

If I expand the groups after this, I see:

B
    A1
    A2
A
    B1
    B2

When what I need to see is this:

B
    B1
    B2
A
    A1
    A2

How can I accomplish this?

Update: As requested in the comments, here is a link to this simple example: Group Sort Test

However, I'm not sure how useful this is - I notice that in view only mode you aren't able to expand/collapse the groups or sort, and I'm not sure it's wise to allow any anonymous reader to have edit access.

Improve this question
5
  • Yes for the simple example, I'll edit this into the question, although I'm not sure how helpful this will be.
    – Michael
    Commented Jul 17, 2018 at 19:32
  • Have you already considered to add an auxiliary column?
    – Rubén - Volunteer Moderator - ♦
    Commented Jul 17, 2018 at 19:36
  • @Rubén I assume by that you mean a column to sort on which will keep the group and all its contents together while retaining sort order? Maybe something like the value of the header field plus a dash and the index of the item in the group? Only thing is, in order for sorting to even look at this field I would first have to expand several hundred groups, then collapse them afterwards.
    – Michael
    Commented Jul 17, 2018 at 22:05
  • I meant something like what I described on my answer to How to sort groups of rows in Google Sheets?
    – Rubén - Volunteer Moderator - ♦
    Commented Jul 17, 2018 at 22:54
  • In your demo spreadsheet, get the version prior to sorting, add this to C1 =ArrayFormula(IF(ISBLANK(B:B),A:A,B:B)), then copy C an do a paste as values only over itself then use it to sort. (This exercise will help to know if your demo spreadsheet is representing appropriately the case to be solved)
    – Rubén - Volunteer Moderator - ♦
    Commented Jul 17, 2018 at 23:01

1 Answer 1

Reset to default
1

First, insert a header row if you do not have one already. Then choose View > Freeze > 1 row and create sort keys with this formula in row 1 of a free column:

=arrayformula( 
  { 
    "Sort key"; 
    if( 
      row(B2:B) <= max(row(B2:B) * (B2:B <> "")), 
      iferror( 
        vlookup( 
          row(A2:A), 
          if( len(A2:A), { row(A2:A), A2:A } ), 
          2, 
          true 
        ) 
      ), 
      iferror(1/0) 
    ) 
  } 
)

Then select a cell in the sort key column and choose Data > Sort sheet by column in A → Z or Z → A order. The values in column B will follow their top-level cell in column A.

Note that the group rows feature does not observe sorting, so in the event your row groups vary in height, row grouping will not be correct after the sort. This is not an issue if you are not using row grouping or if all the groups are the same height.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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