Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

enter image description here (I don't have enough reputation to embed the image directly.)

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.

share|improve this question
    
Welcome to SuperUser and congratulations on writing a clear question. For this to work, in Excel I would set up a sorting rule that indexed the values on the left to those on the right. Need to look if Google does the same, but it should be doable. –  Doktoro Reichard Aug 30 '13 at 14:35
    
You definitely don't have to select each category manually. Just make a column with the category number in it and sort by that first. –  beroe Aug 31 '13 at 3:03
add comment

3 Answers

up vote 1 down vote accepted

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:

    Table - Not sorted

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

    Sort Range

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

    Sort Range Dialog

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

    End Result

  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:

Table - Unsorted data

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.

Sort Range - multiple columns

The end result is something like this:

End result

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.

share|improve this answer
    
This definitely works, but is there any way to do it using the filter function? I'm making a parts sheet for some of my lab mates, and there are going to be about 7 subgroups and probably 10 different criteria, so I'm trying to automate this as much as possible. We can definitely do it this way, it would just be slower/more tedious than my ideal (non-existent?) solution. Thanks! –  gwenger Aug 30 '13 at 15:03
    
The filter function was designed for just that - filtering. What this is is sorting. Maybe your original concept is faulty, so maybe stepping back for 5 minutes, breathing a little air, and looking at your problem from a different perspective might help. (on retrospect, enabling the filter function does allow for easier access to sort, so it's a compromise) –  Doktoro Reichard Aug 30 '13 at 15:14
    
Right, but once I enable filtering, there is a dropdown menu on the column labels that allows me to sort by the data. I guess I was just looking for a combination of the two in order to maximize my options. Thanks for your help. –  gwenger Aug 30 '13 at 15:19
    
I know this was accepted as an answer, but you can automate this –  datatoo Aug 31 '13 at 1:14
    
@datatoo I've read your post and it seems acceptable (although I haven't tested it). The poster made explicit it's preference for Google Spreadsheets, not Excel. Later I'll update this post with some more info you have all contributed. –  Doktoro Reichard Aug 31 '13 at 8:57
add comment

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.

sorting image

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.)

share|improve this answer
add comment

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

enter image description here

Select a cell in the column you wish to sort by

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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