In a spreadsheet, is it possible to separate rows into groups that will not change order when filtered?

Question

I am trying to make row subgroups in my spreadsheet rows so that I can sort my data but have rows in any particular subgroup stay together. This is probably easiest to show with a picture.

Sorting rows within subgroups by column data

Ideally I'd like to find a way to do this in Google spreadsheets, but I'd be willing to bet that it's not possible, so solutions for any type of spreadsheet program are welcome.

Answer 1

Ok, in Google Spreadsheets:

1. I created an initial spreadsheet that should look like this (taking some artistic liberties). The bottom half is yet to be sorted and as such, is equal to the upper half:

   

2. Select the three columns that make the Category 1, then go to Data > Sort range.

   

3. From there, just change the preset column (should be A) to C (aka, the 2nd criteria).

   

4. This part of the table is now sorted according to the 2nd criteria. Just repeat to the 2nd category.

   

5. The end result should be your table.

UPDATE:

Based on @beroe suggestion, I was able to make up a little more.

First of, you need to add an extra column, that assigns Individual Categories to each of the items. Although this is feasible with your current data setup, I find it is the Right Thing To Do to have a table such as the following:

Note that I haven't yet sorted the data according to your 2nd criteria. Note also that next to the 2nd criteria is now a column that contains each item's respective Category.

Select all data, then go to Data > Sort Range. In there Sort first by column D (the column with the Categories), after press add another sort column, and then add the column corresponding to the 2nd criteria (column C).

Why this works is because of an idea spreadsheet software has about precedence. As it is, Google will try to sort first by the column D. Afterwards, it will sort all remaining data by column C. All remaining data is already sorted.

Note that the order by which I placed the columns is this, due to the reason pointed before. If you tried to place Column C then column D, you wouldn't have your desired outcome.

The end result is something like this:

Ultimately, adding a column with the item's information is a good spreadsheet design decision (if there is something like this in existence).

As a final note, it seems Google Spreadsheet has JavaScript functionality by default (i.e., it is the equivalent of Macros for Excel). It can be accessed by Tools > Script Editor. However, I don't know how to write JavaScript. Ultimately, if you need anything more complex, you can resort to that.

Answer 2

The very easiest way would be to add a column that encodes the grouping (e.g., CategoryNum). Then sort by that column first and your Criteria column second.

If you put your Category code in the subheader rows (Category 1), the order of the whole spreadsheet should be preserved.

(Side note: For data processes that you are trying to automate, it is best to organize your data this way anyway, instead of breaking them up visually in ways that make it hard to parse.)

Answer 3

First Create your Named Ranges and then adapt this

Sub SortBySelectedCellColumn()
Dim cell As String
Dim srt As Integer
Dim nm As Name
 cell = ActiveCell.Address
 srt = ActiveCell.Column
For Each nm In ThisWorkbook.Names
   Range(nm).Select
   Selection.Sort _
    Key1:=Worksheets("Sheet1").Columns(srt), _
    Header:=xlGuess
Next
  Range(cell).Select
End Sub

Select a cell in the column you wish to sort by