Ranking by total points and comparing rank to previous week

[charlierb3]

I'm creating a weekly pickem pool and want to rank players from high score to lowest and compare the current weeks ranking to the previous week

So if I enter each player's points each week in a sheet called Scores:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Player	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Total
2	Player 1	9	9	1															19
3	Player 2	5	17	9															31
4	Player 3	1	14	2															17
5	Player 4	9	9	2															20
6	Player 5	9	15	12															36
7	Player 6	0	11	8															19
8	Player 7	10	11	7															28
9	Player 8	11	8	12															31
10	Player 9	8	0	8															16
11	Player 10	2	2	17															21
Scores

Cell Formulas
Range	Formula
S2:S11	S2	=SUM(B2:R2)

I want another sheet called Rank that looks like

Book1
	A	B	C	D	E
1	Rank	Player	Total Points	Rank Last Week
2	1	Player 5	36	1
3	2	Player 2	31	2
4	2	Player 8	31	4
5	3	Player 7	28	3
6	4	Player 10	21	9
7	5	Player 4	20	5
8	6	Player 1	19	5
9	6	Player 6	19	7
10	7	Player 3	17	6
11	8	Player 9	16	8
Rank

I'd like each player's points to get summed each week from the scores table =Sum(!Scores($B$2:$R$11)) and each player ranked 1-10 (ties are fine) based on the totals. But I also want to know how the player's ranking has changed compared to last week. So if it was week 3, I'd want only the scores for weeks 1 and 2 summed. A formula that says "Sum all but the last non-blank column" and rank them.

Any help is appreciated

 

[Ezguy4u]

Just to get the ball rolling. I would go with a VBA code. Now there might be some A students that could do this using functions or formulas but not me. Let's see if this is close to what you want.

Sub Rank_Score()
'
Sheets("Rank").Select
Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D11"), Type:=xlFillDefault
    Range("D2:D11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("A2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RANK(RC[2],R2C3:R11C3,0)"
    Selection.AutoFill Destination:=Range("A2:A11"), Type:=xlFillDefault
    Range("A2:A11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A2:D11").Select
    ActiveWorkbook.Worksheets("Rank").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Rank").Sort.SortFields.Add2 Key:=Range("A2:A11"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Rank").Sort
        .SetRange Range("A2:D11")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
     Application.CutCopyMode = False
     Sheets("Rank").Select
     Range("A1").Select
     
End Sub

20-09-11 rank.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Rank	Player	Total Points	Rank Last Week
2	1	Player 5	24			1	Player 5	24			5	Player 1	18
3	2	Player 2	22			2	Player 2	22			2	Player 2	22
4	3	Player 7	21			3	Player 7	21			7	Player 3	15
5	4	Player 8	19			4	Player 8	19			5	Player 4	18
6	5	Player 1	18			5	Player 1	18			1	Player 5	24
7	5	Player 4	18			5	Player 4	18			8	Player 6	11
8	7	Player 3	15			7	Player 3	15			3	Player 7	21
9	8	Player 6	11			8	Player 6	11			4	Player 8	19
10	9	Player 9	8			9	Player 9	8			9	Player 9	8
11	10	Player 10	4			10	Player 10	4			10	Player 10	4
Rank

20-09-11 rank.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Player	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Total
2	Player 1	9	9																18
3	Player 2	5	17																22
4	Player 3	1	14																15
5	Player 4	9	9																18
6	Player 5	9	15																24
7	Player 6	0	11																11
8	Player 7	10	11																21
9	Player 8	11	8																19
10	Player 9	8	0																8
11	Player 10	2	2																4
12
13
14
15	1
16	9
17	2
18	2
19	12
20	8
21	7
22	12
23	8
24	17
Score

 

[Peter_SSs]

If your Excel 365 has the new dynamic array formulas (eg SORT(), SORTBY etc) then you could do it with worksheet formulas like this.

On the 'Scores' sheet add two columns with formulas as shown copied down then hide those two columns if you want.

charlierb3 1.xlsm
	A	B	C	D	E	F												R	S	T	U
1	Player	Week 1	Week 2	Week 3	Week 4	Week 5												Week 17	Total	Last Week Total	Last Week Rank
2	Player 1	9	9	1															19	18	5
3	Player 2	5	17	9															31	22	2
4	Player 3	1	14	2															17	15	7
5	Player 4	9	9	2															20	18	5
6	Player 5	9	15	12															36	24	1
7	Player 6	0	11	8															19	11	8
8	Player 7	10	11	7															28	21	3
9	Player 8	11	8	12															31	19	4
10	Player 9	8	0	8															16	8	9
11	Player 10	2	2	17															21	4	10
Scores

Cell Formulas
Range	Formula
S2:S11	S2	=SUM(B2:R2)
T2:T11	T2	=S2-LOOKUP(9^9,B2:R2)
U2:U11	U2	=RANK(T2,T$2:T$11)

On 'Rank' sheet
The formulas shown only need to be entered in row 2 of each column & the results will spill down the column (if you have those required functions)

charlierb3 1.xlsm
	A	B	C	D
1	Rank	Player	Total Points	Rank Last Week
2	1	Player 5	36	1
3	2	Player 2	31	2
4	2	Player 8	31	4
5	3	Player 7	28	3
6	4	Player 10	21	10
7	5	Player 4	20	5
8	6	Player 1	19	5
9	6	Player 6	19	8
10	7	Player 3	17	7
11	8	Player 9	16	9
Rank

Cell Formulas
Range	Formula
B2:B11	B2	=SORTBY(Scores!A2:A11,Scores!S2:S11,-1)
C2:C11	C2	=SORT(Scores!S2:S11,,-1)
D2:D11	D2	=INDEX(Scores!U2:U11,MATCH(B2#,Scores!A2:A11,0))
Dynamic array formulas.

 

[Ezguy4u]

I slept on my solution and clarified a few items. Just copy and paste the numbers into the weeks and run the program.

Sub Rank_Score()
'
Sheets("Rank").Select
Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D11"), Type:=xlFillDefault
    Range("D2:D11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("A2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RANK(RC[2],R2C3:R11C3,0)"
    Selection.AutoFill Destination:=Range("A2:A11"), Type:=xlFillDefault
    Range("A2:A11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A2:D11").Select
    ActiveWorkbook.Worksheets("Rank").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Rank").Sort.SortFields.Add2 Key:=Range("A2:A11"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Rank").Sort
        .SetRange Range("A2:D11")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
     Application.CutCopyMode = False
     Sheets("Rank").Select
     Range("A1").Select
     
End Sub

20-09-12 rank.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Player	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Total
2	Player 1																		0
3	Player 2																		0
4	Player 3																		0
5	Player 4																		0
6	Player 5																		0
7	Player 6																		0
8	Player 7																		0
9	Player 8																		0
10	Player 9																		0
11	Player 10																		0
12
13
14
15
16	9	9	1
17	5	17	9
18	1	14	2
19	9	9	2
20	9	15	12
21	0	11	8
22	10	11	7
23	11	8	12
24	8	0	8
25	2	2	17
Score

Cell Formulas
Range	Formula
S2:S11	S2	=SUM(B2:R2)

20-09-12 rank.xlsm
	A	B	C	D
1	Rank	Player	Total Points	Rank Last Week
2		Player 1	0
3		Player 2	0
4		Player 3	0
5		Player 4	0
6		Player 5	0
7		Player 6	0
8		Player 7	0
9		Player 8	0
10		Player 9	0
11		Player 10	0
Rank

Cell Formulas
Range	Formula
C2:C11	C2	=Score!S2