Sort for data in 2 columns

[Rob-UK]

I feel like I've been through 100's of threads trying to find this answer but apologies if Ive missed it!

I have a scoresheet for a quiz. As there are 40 teams, and I want to show the scoreboard on a landscape sized screen, I have split the data for the first 20 teams in columns B-J and the 2nd twenty in columns L to T

The TOTAL POINTS columns are therefore columns J and T. ( J4:J23 and T4:T23 )

I want a macro that will ( when linked to an active control button in A1 which I've created) sort all teams data into descending order based on the TOTAL POINTS column (s)

Is that possible?! I have seen ways to do multiple sorts of data but not one sort on data in 2 columns.

Hope that makes sense.

 

[Peter_SSs]

What Excel version are you using? I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Rob-UK]

What Excel version are you using? I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sorry. Im on 365 ( build 16924.20150 Click-to-Run)

 

[Peter_SSs]

Im on 365

Thanks for updating.

For the future, also investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Could you use something like this? I've used a smaller sample with 10 teams.

Maintain the main scoreboard somewhere in one set of columns as I have below in B10:J20 and for display, use two formulas to sort and split the scoreboard as I have in the top section.

Rob-UK.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Team	1	2	3	4	5	6		Total		Team	1	2	3	4	5	6		Total
3		Team 6	18	17	14	8	18	13		88		Team 1	2	7	20	9	5	2		45
4		Team 10	16	2	10	7	15	17		67		Team 4	14			3	6	15		38
5		Team 3	8	7	2	18	7	19		61		Team 5	7	5	1	2	7	7		29
6		Team 8	11	2	4	9	5	17		48		Team 7	19	1						20
7		Team 2	8	9	8	12	8	2		47		Team 9	2					3		5
8
9
10		Team	1	2	3	4	5	6		Total
11		Team 1	2	7	20	9	5	2		45
12		Team 2	8	9	8	12	8	2		47
13		Team 3	8	7	2	18	7	19		61
14		Team 4	14			3	6	15		38
15		Team 5	7	5	1	2	7	7		29
16		Team 6	18	17	14	8	18	13		88
17		Team 7	19	1						20
18		Team 8	11	2	4	9	5	17		48
19		Team 9	2					3		5
20		Team 10	16	2	10	7	15	17		67
Sheet1

Cell Formulas
Range	Formula
B3:J7	B3	=LET(v,TAKE(SORT(B11:J20,9,-1),5),IF(v="","",v))
L3:T7	L3	=LET(v,TAKE(SORT(B11:J20,9,-1),-5),IF(v="","",v))
J11:J20	J11	=SUM(C11:H11)
Dynamic array formulas.

 

[Rob-UK]

Thanks Peter ! That is a solution. I was hoping I could enter straight into the scoreboard without needing a 2nd data table below however at this point, I am truly grateful to get anything working !!

So I've pasted the data for the 40 teams below the scoresheet covering B28:J67
Then I've adapted your formulae from =LET(v,TAKE(SORT(B11:J20,9,-1),5),IF(v="","",v)) to =LET(v,TAKE(SORT($C$4:$J$23,9,-1),20),IF(v="","",v)) applied to cells C4:J23 ( the first half of the pretty scoreboard) but am getting #SPILL!
I am assuming that the 9 in your formulae is the number of columns the data spans , -1 no idea(!) and the 5 the number of rows I am displaying the scoreboard data on. I obviously got something terribly wrong

 

[Fluff]

but am getting #SPILL!

Clear all cells in C4:J23 & then use this formula in C4 only

=LET(v,TAKE(SORT(B28:J67,9,-1),20),IF(v="","",v))

 

[Peter_SSs]

Then I've adapted your formulae

Yes, you have adapted the formulas incorrectly. It relates mainly to the range that you have pointed it at. @Fluff is on to that but I think that the formula should be in column B to emulate your post 1 image.

So, here it is again with the full 40 teams. There are only 2 formulas entered in the top section. Cells B4 and L4. Excel will automatically 'spill' all the other results to the cells in rows 4:23 so don't drag the formula across or down.

Rob-UK.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3		Team	1	2	3	4	5	6		Total		Team	1	2	3	4	5	6		Total
4		Team 6	18	17	14	8	18	13		88		Team 23	4	16	3					23
5		Team 40	16	2	10	7	15	17		67		Team 31	3	2	17					22
6		Team 3	8	7	2	18	7	19		61		Team 37	19	1						20
7		Team 26	20	14	1		11	13		59		Team 9			18					18
8		Team 34	19	18	20					57		Team 18			2	14				16
9		Team 29	16	8	1		15	14		54		Team 24	4	2	9					15
10		Team 38	11	2	4	9	5	17		48		Team 35	7	2	6					15
11		Team 2	8	9	8	12	8	2		47		Team 14				9	4	1		14
12		Team 1	2	7	20	9	5	2		45		Team 19			3	11				14
13		Team 25	9	3	12		14	7		45		Team 36				14				14
14		Team 28	6	7	10		2	18		43		Team 7		12						12
15		Team 30	7	9	6		17	3		42		Team 11	3		3		3	3		12
16		Team 4	14			3	6	15		38		Team 10					10			10
17		Team 27	13	6	9		4	3		35		Team 39	2					3		5
18		Team 22	12	15	5					32		Team 8								0
19		Team 32	7	7	18					32		Team 12								0
20		Team 33	9	20	2					31		Team 13								0
21		Team 5	7	5	1	2	7	7		29		Team 15								0
22		Team 17			20	6				26		Team 16								0
23		Team 20			15	11				26		Team 21								0
24
25
26
27		Team	1	2	3	4	5	6		Total
28		Team 1	2	7	20	9	5	2		45
29		Team 2	8	9	8	12	8	2		47
30		Team 3	8	7	2	18	7	19		61
31		Team 4	14			3	6	15		38
32		Team 5	7	5	1	2	7	7		29
33		Team 6	18	17	14	8	18	13		88
34		Team 7		12						12
35		Team 8								0
36		Team 9			18					18
37		Team 10					10			10
38		Team 11	3		3		3	3		12
39		Team 12								0
40		Team 13								0
41		Team 14				9	4	1		14
42		Team 15								0
43		Team 16								0
44		Team 17			20	6				26
45		Team 18			2	14				16
46		Team 19			3	11				14
47		Team 20			15	11				26
48		Team 21								0
49		Team 22	12	15	5					32
50		Team 23	4	16	3					23
51		Team 24	4	2	9					15
52		Team 25	9	3	12		14	7		45
53		Team 26	20	14	1		11	13		59
54		Team 27	13	6	9		4	3		35
55		Team 28	6	7	10		2	18		43
56		Team 29	16	8	1		15	14		54
57		Team 30	7	9	6		17	3		42
58		Team 31	3	2	17					22
59		Team 32	7	7	18					32
60		Team 33	9	20	2					31
61		Team 34	19	18	20					57
62		Team 35	7	2	6					15
63		Team 36				14				14
64		Team 37	19	1						20
65		Team 38	11	2	4	9	5	17		48
66		Team 39	2					3		5
67		Team 40	16	2	10	7	15	17		67
Sheet3

Cell Formulas
Range	Formula
B4:J23	B4	=LET(v,TAKE(SORT(B28:J67,9,-1),20),IF(v="","",v))
L4:T23	L4	=LET(v,TAKE(SORT(B28:J67,9,-1),-20),IF(v="","",v))
J28:J67	J28	=SUM(C28:H28)
Dynamic array formulas.

I am assuming that the 9 in your formulae is the number of columns the data spans

Not quite, although you certainly do have 9 columns. The 9 is telling the formula to sort all the columns based on the 9th column of the range. Since the range is columns B:J, the 9th column of that is column J (Total)

-1 no idea(!)

The -1 is telling the formula to sort the totals from Highest to Lowest (not the default Lowest to Highest)

the 5 the number of rows I am displaying the scoreboard data on

Correct. It is saying to take the first 5 rows of the sorted data. The -5 in the second formula was saying to take the last 5 rows of the sorted data.

I was hoping I could enter straight into the scoreboard without needing a 2nd data table below

Not quite sure what the issue there is but another possibility may be to have the data entry table on one worksheet and the rearranged (sorted) results on another worksheet for display purposes?

 

[Rob-UK]

You are A M A Z I N G !!! I am always impressed with what Excel can do, with the right knowledge and skills, which you, and so many on this site obviously possess! the scoreboard works a treat, and the large charity quiz I am using it for on Saturday will be so much better for it, so thank you

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.
Good luck with the charity quiz!