Sorting A list by Groups

Magoo · May 5th, 2002, 12:39 AM

Hi

I am currently trying to sort a list that has been broken down into groups. The spreadsheet is to keep tally of the scores in a competition (both for individuals and team events)

Col A Col B Col C Col D
Name Team Name Score Blank
Name Team Name Score Blank
Name Team Name Score Subtotal of Team

This format is repeated for each team. I would like to sort the list to keep the teams (groups) together and end up with a list that represents 1st, 2nd, 3rd and so on. Can anyone shed some light on this for me

[ This Message was edited by: Magoo on 2002-05-04 23:48 ]

Nimrod · May 5th, 2002, 12:47 AM

Select the columns you which to include then utilize the Data..Sort Item from the tool bar.

Nimrod · May 5th, 2002, 12:49 AM

You really don't need to include a sub-total row of information for each team ... just use the ... Data..subtotal feature from the toolbar.

Magoo · May 5th, 2002, 12:52 AM

When I use the Data>Sort function the spreadsheet sorts all the subtotals and places them at the top of the spreadsheet and the rest of the data below. It separates the subtotal from the rest of the data

Nimrod · May 5th, 2002, 01:02 AM

First do not put any totals or subtotals in the data.
Second : utilize the sort feature on data
Third: use the data ..subtotal.. feature to have excel create the subtotals for you.

Magoo · May 5th, 2002, 01:21 AM

Okey Dokey

I have done this, now I have a spreadsheet with all the team scores all subtotaled nicely . . . . .

Now, how do I sort all of this to give me a 1st, 2nd, 3rd, etc whilst keeping all my teams grouped together :?

Nimrod · May 5th, 2002, 01:45 AM

How about having a seperate column for a teams total score then you can use that as one of the columns to sort by ?

example:
colA colb colc
teama 10 23
teamb 11 25
teama 13 23
teamb 14 25

the subtotal column .. in THIS example could be calculated with this formula in each cell of colC : =SUMIF(A:A,A:A,B:B)
A:A = col where team names are
B:B = col where scores are

Then all you would need to do is sort first by subtotal then by Team and Team individual game scores.

Chris Davison · May 5th, 2002, 04:44 AM

Also have a look at Pivot tables, they love this sort of thing....

specifically, you can total by team and have it sorted according to the results, with a bit of trial and error if you've not used them before

Magoo · May 6th, 2002, 08:33 PM

After many hours playing with the SUMIF function and many, many more hours looking at pivot tables I have reached the following solution.

If I simply calculate the subtotals for each team and place this subtotal into the last column next to each and every team member, then sort from there. This is a bit cumbersome, but works.

thanks for your help

May 5th, 2002, 04:44 AM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Also have a look at Pivot tables, they love this sort of thing.... specifically, you can total by team and have it sorted according to the results, with a bit of trial and error if you've not used them before __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 5th, 2002, 12:39 AM	#1
Magoo New Member Join Date: May 2002 Posts: 28	Hi I am currently trying to sort a list that has been broken down into groups. The spreadsheet is to keep tally of the scores in a competition (both for individuals and team events) Col A Col B Col C Col D Name Team Name Score Blank Name Team Name Score Blank Name Team Name Score Subtotal of Team This format is repeated for each team. I would like to sort the list to keep the teams (groups) together and end up with a list that represents 1st, 2nd, 3rd and so on. Can anyone shed some light on this for me [ This Message was edited by: Magoo on 2002-05-04 23:48 ]

May 5th, 2002, 12:47 AM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Select the columns you which to include then utilize the Data..Sort Item from the tool bar.

May 5th, 2002, 12:49 AM	#3
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	You really don't need to include a sub-total row of information for each team ... just use the ... Data..subtotal feature from the toolbar.

May 5th, 2002, 12:52 AM	#4
Magoo New Member Join Date: May 2002 Posts: 28	When I use the Data>Sort function the spreadsheet sorts all the subtotals and places them at the top of the spreadsheet and the rest of the data below. It separates the subtotal from the rest of the data

May 5th, 2002, 01:02 AM	#5
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	First do not put any totals or subtotals in the data. Second : utilize the sort feature on data Third: use the data ..subtotal.. feature to have excel create the subtotals for you.

May 5th, 2002, 01:21 AM	#6
Magoo New Member Join Date: May 2002 Posts: 28	Okey Dokey I have done this, now I have a spreadsheet with all the team scores all subtotaled nicely . . . . . Now, how do I sort all of this to give me a 1st, 2nd, 3rd, etc whilst keeping all my teams grouped together :?

May 5th, 2002, 01:45 AM	#7
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	How about having a seperate column for a teams total score then you can use that as one of the columns to sort by ? example: colA colb colc teama 10 23 teamb 11 25 teama 13 23 teamb 14 25 the subtotal column .. in THIS example could be calculated with this formula in each cell of colC : =SUMIF(A:A,A:A,B:B) A:A = col where team names are B:B = col where scores are Then all you would need to do is sort first by subtotal then by Team and Team individual game scores.

May 6th, 2002, 08:33 PM	#9
Magoo New Member Join Date: May 2002 Posts: 28	After many hours playing with the SUMIF function and many, many more hours looking at pivot tables I have reached the following solution. If I simply calculate the subtotals for each team and place this subtotal into the last column next to each and every team member, then sort from there. This is a bit cumbersome, but works. thanks for your help