Auto Sorting League Table

[Simon Pipe]

Hi,

I run football prediction competitions at work for every major tournament such as World Cup and Euro's It is just for fun and I have done since 2002. Over the years I have tweaked and added to my spreadsheets and works really well with me needing to less and less and excel doing it all for me!

The one thing now though I would like to refine is the league table. I am unable to use XL2BB due to 'IT restrictions' on work laptop so will include screen grabs.

Background is each player predicts whether a team will win, lose or draw each match and based on that against actual results get points. 10 if correct, zero if wrong. They send me all their predictions and they get their own sheet in workbook. Can be up to 100 players. Below is Tab P1

I have a master sheet called 'Match results' and I enter the actual result in it which then populates their scoresheet respectively in column H above and then allocated points.

The sheet 'Table' (below) totals all the scores per player and round from each players sheet. Table A is the main one.

This is where I need help....

I have to manually copy across the data from the table (Table A) from C5 to K14 in to a dummy table (Table B) N5 to V10 using paste 123 before I enter any scores. This way, when I have entered them, I can do a sort on Table A which compares against B to show who has moved up/down the table based on new points totals.

The sort is Column I then H, then G...F...E...D. Except for the last sort for final table whereby Column K is included.

There must be a better way of doing the table so that I don't have to sort manually or copy across data and it does it for me?

Formula I use at the moment:
B5 =IF(MATCH(C5,N:N,0)-ROW()>0,"▲ "&MATCH(C5,N:N,0)-ROW(),IF(MATCH(C5,N:N,0)-ROW()<0,"▼ "&ABS(MATCH(C5,N:N,0)-ROW()),"-")) (Has conditional formatting on it for colours)
C5 ='P4'!$D$2
D5 =IFERROR(@INDIRECT("'"&C5&"'!k43"),0)
E5 =IFERROR(@INDIRECT("'"&C5&"'!k56"),0)
I5 =IFERROR(@INDIRECT("'"&C5&"'!k80"),0)

Hopefully this makes sense and any help would be gratefully received!

Thanks, Simon

 

[Simon Pipe]

Sorry to bump - hoping it does make sense but if I need to refine let me know!

 

[arun_eagertolearn]

Hi

Please keep records of different rounds in different sheets- 'Round1', 'Round2', 'Round3' etc

Auto sorting
Please have two sections in these sheets- have two sections col M to W- the array in itself and the sorted array - col A to K

in col C, pls put formula =SORTBY(O3:W14,T3:T14,-1,S3:S14,-1....)
This would sort the input given in cells O3 to W14 and output the same in cells C3 to K14

Col A has the rank no and Col B has the position up/down, which would be explained below

T3:T14 is the first column to be checked for sorting
S3:S14 the second and so on
-1 implies descending order

Getting the position up/down v previous round

Eg: Round3 has been completed
In a cell (say cell J1) in Round3 sheet, pls put a formula, =MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename")))- this will give the name of the sheet, ie, Round3 in this case

In cell J2, pls put ="Round"&SUBSTITUTE(J1,"Round","")*1-1) this will give Round2, ie, the previous round from Round3

Pls modify the formula and put in B5
=IF(MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW()>0,"▲ "&MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW(),IF(MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW()<0,"▼ "&ABS(MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW()),"-"))

This looks at the previous round sheet, as pointed by cell J2, compares it against the current round and gives the position up/down

Please check and do let know

Please find a depiction below (only two orders of sorting have been given)

Rounds.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1									Current Round Name	Round3
2									Previous Round	Round2
3
4													Current Position
5	1	▲ 3	P4	110	10								1		P4	110	10
6	2	▲ 1	P3	80	5								2		P3	80	5
7	3	▼ 2	P1	60	5								3		P2	60	4
8	4	▼ 2	P2	60	4								4		P1	60	5
9	5	-	P5	0	0								5		P5
10	6	-	P6	0	0								6		P6
11	7	-	P7	0	0								7		P7
12	8	-	P8	0	0								8		P8
13	9	-	P9	0	0								9		P9
14	10	-	P10	0	0								10		P10
Round3

Cell Formulas
Range	Formula
J1	J1	=MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename")))
J2	J2	="Round"&SUBSTITUTE(J1,"Round","")*1-1
C5:E14	C5	=SORTBY(O5:Q14,P5:P14,-1,Q5:Q14,-1)
B5:B14	B5	=IFERROR(IF(MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW()>0,"▲ "&MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW(),IF(MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW()<0,"▼ "&ABS(MATCH(C5,INDIRECT($J$2&"!c:c"),0)-ROW()),"-")),"-")
Dynamic array formulas.