Sorting a group of rows together

G

gymjohn

New Member
Joined
Nov 17, 2017
Messages
1
Hi, I'm not entirely sure how to phrase this and can't find the solution through search

Basically, I have a number of teams with 3 people per team. Each team member has a score which ads up to a total score, I would like to sort my teams from highest score to lowest without disrupting the teams

Ciara, 2
Emma, 4
John, 1
Total, 7

James, 3
Steve, 3
Sarah, 2
Total, 8

How do I sort this by total score while keeping team members together?

Thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
If your data looks like this:

Excel 2012
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #F2F2F2"]Name[/TD]
[TD="bgcolor: #F2F2F2"]Score[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] "]Ciara[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] "]Emma[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] "]John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] "]Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]James[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]Steve[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]Sarah[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] "]Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]8[/TD]

</tbody>
Hoja1​



You can use this to sort it in groups * AS LONG AS ALL TEAMS HAVE 3 MEMBERS*

Code: 
Sub [COLOR=#574123]gymjohn[/COLOR]()
Dim lRow, i As Long


lRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
For i = 2 To lRow Step 4


    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(i, 2), Cells(i + 2, 2)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(Cells(i, 2), Cells(i + 2, 2))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next


End Sub

You should get this result

Excel 2012
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #F2F2F2"]Name[/TD]
[TD="bgcolor: #F2F2F2"]Score[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] "]Ciara[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] "]Emma[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] "]John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6EFCE]#C6EFCE[/URL] , align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] "]Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]James[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]Steve[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]Sarah[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] "]Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: right"]8[/TD]

</tbody>
Hoja1​
 
Upvote 0
I just noticed I made a mistake

If you have this:

Excel 2012
AB
NameScore
Emma
Ciara
John
Total
James
Steve
Sarah
Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]

</tbody>
Hoja1 (3)​



Use this * AS LONG AS ALL TEAMS HAVE 3 MEMBERS*:

Code: 
Sub Macro1()


Dim lrow, i As Long


lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
For i = 2 To lrow Step 4


    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(i, 2), Cells(i + 2, 2)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(Cells(i, 1), Cells(i + 2, 2))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next


End Sub

To get this:

Excel 2012
AB
NameScore
Ciara
Emma
John
Total
James
Steve
Sarah
Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]

</tbody>
Hoja1 (3)​
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Locked
  • Question Question
Revised Golf scoring
Replies
1
Views
320
Fluff
Fluff
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
352
STARL0RD
S
J
  • Question Question
Fantasy Football Standings with multiple scoring points
Replies
1
Views
1K
jdellasala
J
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
324
Jagadeshrao
J

Forum statistics

Threads
1,223,711
Messages
6,174,028
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top