0

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.

  • Yes for the simple example, I'll edit this into the question, although I'm not sure how helpful this will be. – Michael Jul 17 '18 at 19:32
  • Have you already considered to add an auxiliary column? – Rubén Jul 17 '18 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 Jul 17 '18 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 Jul 17 '18 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 Jul 17 '18 at 23:01

Your Answer

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

Browse other questions tagged or ask your own question.