sort by different cells. NOT by row

[mspincus]

I am in a Golf Pool at work. You have to add up the scores and the lowest wins. We have 4 players and then add their scores up. is there a way to add the scores and then sort all totals and list leaders

Exp. Team Scott thursday Friday Saturday Sunday

Gollfer 1 -5 -2 3 -4

Golfer 2 4 -2 1 0

Golfer 3 -5 -1 -1 -1

Golfer 4 -5 0 -2 -1

There are many teams. So I need to add Thursday total which is -11 and place in order all the teams for thursday then add those to friday and rank all the teams and then saturday etc.

 

[KMacauley]

Hi mspincus,

In regards to your issue, I have come up with the following solution. Note: I am using Excel 2013 PowerPivot, so ensure you have PowerPivot enabled (Excel 2010 requires a Microsoft add-in).

Assumptions:

• I had to normalize the raw data so that Team Name was another column
• The calculation for the team score is a SUM, not an AVG

1. Select your whole data
2. Format as table (Home > Format as Table)
3. Name your table (Design > TableName)
4. Add to data model (Powerpivot > Add to Data Model)
5. View Powerpivot interface if not already in focus (PowerPivot > Manage)
6. At the end of the first row, add in the calculation
   =RANKX(ALL(GolfScores), CALCULATE(SUM([Thur]), FILTER(ALL(GolfScores), [Team Name]=EARLIER([Team Name]))), , 1, Dense)
7. Name the column 'Thurs Rank'
8. In the calculation area under 'Thurs Rank', add the calculation
   Thurs Team:=LOOKUPVALUE([Team Name], [Thurs Rank], 1)
9. Bring your spreadsheet back into focus
10. On the row directly after the table under the Thur column, add the following calculation
    =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Thurs Team]")
11. Repeat steps 4 to 8, but for Fri, Sat and Sun

Result:
[TABLE="class: grid, width: 594"]
<tbody>[TR]
[TD]Team Name[/TD]
[TD]Golfer Name[/TD]
[TD]Thur[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]Golfer 1[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-4[/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]Golfer 2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]Golfer 3[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]Golfer 4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Golfer 5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Golfer 6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Golfer 7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Golfer 8[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Golfer 9[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Golfer 10[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Golfer 11[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Golfer 12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Scott[/TD]
[TD]Robert[/TD]
[TD]Robert[/TD]
[TD]Jack[/TD]
[/TR]
</tbody>[/TABLE]


To provide the below requirement, I have done the following;

So I need to add Thursday total which is -11 and place in order all the teams for thursday then add those to friday and rank all the teams and then saturday etc.

1. View Powerpivot interface if not already in focus (PowerPivot > Manage)
2. Create pivot table (Home > PivotTable)
3. Add Thurs Rank and Team Name to Rows quadrant
4. Add Thurs to Values quadrant (optional)
5. Reformat the table layout
   1. Select the Design tab
   2. Remove subtotals (Subtotals > Do Not Show Subtotals)
   3. Remove grand totals (Grand Totals > Off for Rows and Columns)
   4. Change table to tabular layout (Report Layout > Show in Tabular Form)
   5. Repeat row labels (Report Layout > Repeat All Item Labels)
   6. As an optional extra, change the pivot table style from the list of PivotTable Styles selection (I prefer Pivot Style Medium 6)
6. Repeat steps 2 to 5, but for Fri, Sat and Sun

Result:
[TABLE="class: grid, width: 266"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Thurs Rank[/TD]
[TD]Team Name[/TD]
[TD]Sum of Thur[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Scott[/TD]
[TD="align: right"]-11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Jack[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Robert[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 263"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Fri Rank[/TD]
[TD]Team Name[/TD]
[TD]Sum of Fri[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Robert[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Scott[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Jack[/TD]
[TD="align: right"]1

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 266"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Sat Rank[/TD]
[TD]Team Name[/TD]
[TD]Sum of Sat[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Robert[/TD]
[TD="align: right"]-11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Jack[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Scott[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 263"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Sun Rank[/TD]
[TD]Team Name[/TD]
[TD]Sum of Sun[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jack[/TD]
[TD="align: right"]-11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Scott[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Robert[/TD]
[TD="align: right"]-5[/TD]
[/TR]
</tbody>[/TABLE]


If you want to get fancy, instead of selecting pivot table option, select 'Four Charts' and do steps 3 to 5.


Here is my example file:
https://www.dropbox.com/s/l9hsxs7pydxselm/Golf Example.xlsx


Kim.