Rank players by different criteria

[svsyy]

Hi all,
I am trying to create an excel data table that ranks players for each criteria on its own (points, rebounds, steals, assists, and so forth).
Sample data for simplicity sake.
table 1:
players points rebounds steals assists
playera 15 3 2 1
playerb 12 5 5 3
playerc 7 10 5 1

desired output in 2 tables
tableoutput.1
points rebounds steals assists
15 10 5 3
12 5 5 1
7 3 2 1

tableoutput.2
points, rebounds, steals assists
playera playerc playerb playerb
playerb playerb playerc playera
playerc playera playera playerc

I can create the first desired data table using sort descending function.
I am getting stuck with the second desired data table. I tried using index match however repeat values give the first result that matches rather than the next one. So for steals, playerb comes out twice instead of playerb then playerc (similar for assists with playera coming out twice instead of playera and then playerc).

I can create player columns after each category and then sort descending, however I have a lot more criteria ie columns that I would need to sort so manually creating columns and then sorting for each criteria becomes very time consuming. Hoping for a more efficient solution.

Thanks in advance for your help!!!

 

[Toadstool]

Hi Svsyy,

Does this work for you?

Svsyy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table1						Tableoutput1					Tableoutput2
2	players	points	rebounds	steals	assists		points	rebounds	steals	assists		points	rebounds	steals	assists
3	playera	15	3	2	1		15	10	5	3		playera	playerc	playerb	playerb
4	playerb	12	5	5	3		12	5	5	1		playerb	playerb	playerc	playera
5	playerc	7	10	5	1		7	3	2	1		playerc	playera	playera	playerc
Sheet1

Cell Formulas
Range	Formula
G3:J5	G3	=AGGREGATE(14,6,B$3:B$5,ROW()-ROW($G$2))
L3:O5	L3	=INDEX($A$3:$A$5,AGGREGATE(15,6,ROW($A$3:$A$5)-ROW($A$2)/(B$3:B$5=G3),COUNTIF(G$2:G3,G3)))

 

[C Moore]

With 365
=INDEX(SORT(Table 1,2),,1)
This will give you a single column (reduced to a single column by INDEX) ordered by rebounds (the 2nd column). Use a 'counter' for the 2 and you could drag it over to create all the columns, or an index/match of headers.

 

[svsyy]

Hi Svsyy,

Does this work for you?

Svsyy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table1						Tableoutput1					Tableoutput2
2	players	points	rebounds	steals	assists		points	rebounds	steals	assists		points	rebounds	steals	assists
3	playera	15	3	2	1		15	10	5	3		playera	playerc	playerb	playerb
4	playerb	12	5	5	3		12	5	5	1		playerb	playerb	playerc	playera
5	playerc	7	10	5	1		7	3	2	1		playerc	playera	playera	playerc
Sheet1

Cell Formulas
Range	Formula
G3:J5	G3	=AGGREGATE(14,6,B$3:B$5,ROW()-ROW($G$2))
L3:O5	L3	=INDEX($A$3:$A$5,AGGREGATE(15,6,ROW($A$3:$A$5)-ROW($A$2)/(B$3:B$5=G3),COUNTIF(G$2:G3,G3)))

awesome! thanks!!!
all else the same, what if steals for playerb is blank how do i get it so that in table2 output for steals to look like c a and blank for the 3rd position?

 

[svsyy]

With 365
=INDEX(SORT(Table 1,2),,1)
This will give you a single column (reduced to a single column by INDEX) ordered by rebounds (the 2nd column). Use a 'counter' for the 2 and you could drag it over to create all the columns, or an index/match of headers.

thanks!
quick question. when i tried your method with the counter
=INDEX(SORT($A$3:$E$5,B15),,1) with b15:e15 incrementing by 1 2 3 4 (results below)
result for points and rebounds is right, however steals and assists should be b c a and b a c respectively. however what i get is below. am i doing something wrong?

points	rebounds	steals	assists
a	c	a	a
b	b	b	b
c	a	c	c

thanks!

 

[Toadstool]

awesome! thanks!!!
all else the same, what if steals for playerb is blank how do i get it so that in table2 output for steals to look like c a and blank for the 3rd position?

So Tableoutput1 would also have an empty cell for steals (so would be 5,2,empty) so change G3 to be
=IFERROR(AGGREGATE(14,6,B$3:B$5,ROW()-ROW($G$2)),"")
...and just copy down and across

 

[C Moore]

=INDEX(SORT($A$3:$E$5,COLUMNS($B:B),,1)
COLUMNS($B:B) this fixes it to start at B and counts up as it's dragged across. (Also works with $B15:B15, etc.)
COLUMNS($B:B)=1
COLUMNS($B:C)=2
COLUMNS($B:D)=3
It sounds like you did the same thing in separate cells. As to why it didn't work, have you ever stepped through with evaluate formula? It might help you find what isn't changing correctly. I'm not implying you don't know how, I'm surprised how often people don't know about it. It helps me fix things a lot. Be sure and use a limited data range to make it easier to parse out.

 

[svsyy]

So Tableoutput1 would also have an empty cell for steals (so would be 5,2,empty) so change G3 to be
=IFERROR(AGGREGATE(14,6,B$3:B$5,ROW()-ROW($G$2)),"")
...and just copy down and across

Great thanks! Appreciate the quick feedback.
With the change to g3 i got the following:

players

points

rebounds

steals

assists

points

rebounds

steals

assists

points

rebounds

steals

assists

a

15​

3​

2​

1​

15​

10​

5​

3​

a

c

c

b

b

12​

5​

3​

12​

5​

2​

1​

b

b

a

a

c

7​

10​

5​

1​

7​

3​

1​

c

a

b

c

So the steals is blank for table1 output but table2 output still shows b. Is there something I need to change in L3 formula for table2 output to have steals to have just c and a and b blank ie under steals [c a ""] not [c a b]?
Thanks again!

 

[Toadstool]

Logically player b still comes last for steals but if you want empty cells to be treated as not applicable then you can just use an IF statement:

Svsyy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table1						Tableoutput1					Tableoutput2
2	players	points	rebounds	steals	assists		points	rebounds	steals	assists		points	rebounds	steals	assists
3	a	15	3	2	1		15	10	5	3		a	c	c	b
4	b	12	5		3		12	5	2	1		b	b	a	a
5	c	7	10	5	1		7	3		1		c	a		c
Sheet1 (2)

Cell Formulas
Range	Formula
G3:J5	G3	=IFERROR(AGGREGATE(14,6,B$3:B$5,ROW()-ROW($G$2)),"")
L3:O5	L3	=IF(G3="","",INDEX($A$3:$A$5,AGGREGATE(15,6,ROW($A$3:$A$5)-ROW($A$2)/(B$3:B$5=G3),COUNTIF(G$2:G3,G3))))

 

[svsyy]

=INDEX(SORT($A$3:$E$5,COLUMNS($B:B),,1)
COLUMNS($B:B) this fixes it to start at B and counts up as it's dragged across. (Also works with $B15:B15, etc.)
COLUMNS($B:B)=1
COLUMNS($B:C)=2
COLUMNS($B:D)=3
It sounds like you did the same thing in separate cells. As to why it didn't work, have you ever stepped through with evaluate formula? It might help you find what isn't changing correctly. I'm not implying you don't know how, I'm surprised how often people don't know about it. It helps me fix things a lot. Be sure and use a limited data range to make it easier to parse out.

Thanks for the feedback and fast reply! When I tried evaluate for steals, this is what i see with evaluate
1. index(sort({"a", 15 3 2 1;"b", 12,5,5,3;"c",7,10,5,1},3),,1)
2. index({"a", 15 3 2 1;"b", 12,5,5,3;"c",7,10,5,1},,1)
3. a
for some reason step 1 to 2 is failing (works for points and rebounds). it is not sorting by descending order on column 3 ie [2 5 5]. I am not sure why this doesn't work for steals and assists. It works for points and rebounds. when I looked at sort function to use descending parameter i should use -1 ie =INDEX(SORT($A$3:$E$5,COLUMNS($B16:B16),-1),,1) but that makes order ascending for points and rebounds and descending for steals and assists.
not sure how sorting is working for arrays.
Thanks.