How do I sort Groups in Excel?

Question

I am trying to create a spreadsheet that will keep track of the best teams in baseball to bet on for No Runs in the First Inning. I have grouped all of the starting pitchers with their respective teams and input all the data to start the season. Now I need help trying to sort the teams by any of the given columns, as the spreadsheet remains the same whenever I try and sort them from largest to smallest or vice versa.

Answer 1

You need a helper column of sort numbers, so keep your groups in order, and the headers in the right place, but to allow sorting based on any other column. Your helper column should always be the first sort range, ascending, and then the other column(s) should be secondary sorts. You also need a slight adjustment to your data set, so that the pitchers list for any team starts one row below the team row

For example, with a new column A, and B filled with what had been in A:

1 Team

2 Reds

3 Reds Pitcher 1

3 Reds Pitcher 2

3 Reds Pitcher 3

3 Reds Pitcher 4

3 Reds Pitcher 5

4 <Fully blank row>

4 <Fully blank row>

5 Nationals

6 Nats Pitcher 1

6 Nats Pitcher 2

6 Nats Pitcher 3

6 Nats Pitcher 4

6 Nats Pitcher 5

7 <Fully blank row>

7 <Fully blank row>

With that layout, the Reds' pitchers will be sorted only among themselves, as will the Nats' pitchers, etc.

Answer 2

Bernie,

How I was hoping this would work. I still have the same issue though, although your solution makes sense. The data is just not moving around at all. I've even tried sorting the data in descending order so the helper row would flip and it just does nothing. Maybe it's the groups and I should remove them, as shown on the left-hand side of the original image, or just group the helper cells, for example:

1 Team

2 Reds

3 Reds Pitcher 1

3 Reds Pitcher 2

3 Reds Pitcher 3

3 Reds Pitcher 4

3 Reds Pitcher 5

4 <Fully blank row>

4 <Fully blank row>

5 Nationals

6 Nats Pitcher 1

6 Nats Pitcher 2

6 Nats Pitcher 3

6 Nats Pitcher 4

6 Nats Pitcher 5

7 <Fully blank row>

7 <Fully blank row>

Should I group only the helper cells 2, 3, and 4? All of the data in rows 2,3 and 4?  Maybe all of the data besides the helper cells?  Or no data at all?

This particularly stumps me, I appreciate your help and quick response though.

Thanks,

Jack

Answer 3

I usually only group data in pivot tables or for quickly hiding/unhiding specific rows on a sheet.

Remove your grouping, and then add similar functionality by using event code - for example, with the team names in column B, separated by blank cells, with 5 rows of pitcher stats:

1. Copy this code.
2. Right-Click the sheet tab of interest.
3. Select "View Code"
4. Paste the code into the window that appears.
5. Save the file as a macro-enabled .xlsm file.
6. Make changes as needed

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 

If Target.Column <> 2 Then Exit Sub 

If Target.Value = "" Then Exit Sub 

With Target.Offset(1).Resize(5).EntireRow 

    .Hidden = Not .Hidden 

End With 

End Sub

Selecting a team name will hide the 5 rows directly below - the only drawback is that you need to select another cell before 'toggling' the view of a team, since the selection needs to change to fire the code.