Determining Rankings

[Phil042]

Good afternoon all

I am very new to Excel and have been trust into the fire on a learn as you go. I have been given a database of men and women. there are 2 pools in each catagory A&B also there is a standings that tabulates the numbers.

My problem is that i can only get the standings of the men or women, not both. Sheets 2&3 are men's A&B Pools, Sheets 5&6 are women's. Sheet 1 displays the men's standings and sheet 4 displays the women's standings.

The standings are displayed. the names & numbers are correct but the SORT is not corect.

I can only print the standings of the men or women not both.

Does that make sense to anyone ????

 

[StephenCrump]

Welcome to the Forum!

I am very new to Excel ...

I am guessing this means you didn't write the VBA code, and you are trying (unsuccessfully) to use the code already in the workbook?

If so, is there any reason you can't ignore the code, and sort the four tables (i.e. two pools x Male/Female) manually?

Here's a sample layout that I want to sort on column F:

	A	B	C	D	E	F	G
1
2		Competitor	Club	Blah	Blah	Standing
3		A	Blah	Blah	Blah	3
4		B	Blah	Blah	Blah	1
5		C	Blah	Blah	Blah	4
6		D	Blah	Blah	Blah	6
7		E	Blah	Blah	Blah	2
8		F	Blah	Blah	Blah	5
9
Sheet1

In Excel, select the range B2:F8, choose Data/Sort:

Click the "OK" button, and the result will be:

	A	B	C	D	E	F
1
2		Competitor	Club	Blah	Blah	Standing
3		B	Blah	Blah	Blah	1
4		E	Blah	Blah	Blah	2
5		A	Blah	Blah	Blah	3
6		C	Blah	Blah	Blah	4
7		F	Blah	Blah	Blah	5
8		D	Blah	Blah	Blah	6
9
Sheet1

 

[Phil042]

I am hoping the following makes sense

2 pools of Men and 2 pools of women. Each pool calculates the total distances of throws and gives us the "Rank Distance" Using a formula we can determine the rankings.



The data from Pools A, B, C & D are compiled into a Men's or Women's Standings. # of throws, total distance over # of throws. Subtract the 2 highest throws gives us the rank distance which in turn gives us the pool ranking. The problem I have is that I can not get the rankings of both men and women. It is one or the other

I've attached the macro which used to provide the pool Ranking

Also attached are the formulas

 

[Phil042]

Welcome to the Forum!

I am guessing this means you didn't write the VBA code, and you are trying (unsuccessfully) to use the code already in the workbook?

If so, is there any reason you can't ignore the code, and sort the four tables (i.e. two pools x Male/Female) manually?

Here's a sample layout that I want to sort on column F:

	A	B	C	D	E	F	G
1
2		Competitor	Club	Blah	Blah	Standing
3		A	Blah	Blah	Blah	3
4		B	Blah	Blah	Blah	1
5		C	Blah	Blah	Blah	4
6		D	Blah	Blah	Blah	6
7		E	Blah	Blah	Blah	2
8		F	Blah	Blah	Blah	5
9
Sheet1

In Excel, select the range B2:F8, choose Data/Sort:

View attachment 122514
Click the "OK" button, and the result will be:

	A	B	C	D	E	F
1
2		Competitor	Club	Blah	Blah	Standing
3		B	Blah	Blah	Blah	1
4		E	Blah	Blah	Blah	2
5		A	Blah	Blah	Blah	3
6		C	Blah	Blah	Blah	4
7		F	Blah	Blah	Blah	5
8		D	Blah	Blah	Blah	6
9
Sheet1

Stephen. Please check out my latest post.. Thanks

 

[StephenCrump]

Let's take this step by step ...

Sheets 2&3 are men's A&B Pools, Sheets 5&6 are women's. Sheet 1 displays the men's standings and sheet 4 displays the women's standings.

I am assuming that Sheet1 shows two men's results tables - one for Pool A and one for Pool B.

If the code you've posted is sorting correctly, then this men's summary sheet is called "Men Standings Round Robin", the two tables are in ranges B7:G16 and B20:G29, and both are sorted on column G.

When you say in Post #1 that the "sort is not correct", it's not clear whether:

a) You're referring to these tables and the code is not working, or
b) The code is working and the incorrect sort is in some higher level table(s)?

I've shown you how to do a manual sort, so I am assuming that one way or another, you can get two correctly sorted tables on the "Men Standings Round Robin" sheet?

I have assumed that similar will apply to the women's results, and that one way or another, you can get two correctly sorted tables on Sheet4 (which is perhaps named "Women Standings Round Robin"?

What is not clear is:

The problem I have is that I can not get the rankings of both men and women. It is one or the other

It sounds like you have a higher level summary table that aggregates results for Pool A and Pool B, with the option of doing this for men, or for women, and you want the third option of combining men and women?

Is this being done by VBA or by formulae? Is this working for men only and for women only? Or is this where the table is collating correctly but the sort is not working?

If you will always be using Excel 365, this could be done relatively easily with formulae:

	A	B	C	D	E	F	G	H
1				Include	Both
2		Combined
3		Team	Blah	Blah	Blah	Rank distance	LSD rank
4		B	WA blah	WA blah	WA blah	540	1
5		L	MB blah	MB blah	MB blah	550	2
6		A	WA blah	WA blah	WA blah	570	3
7		K	MB blah	MB blah	MB blah	580	4
8		E	WB blah	WB blah	WB blah	600	5
9		D	WB blah	WB blah	WB blah	700	6
10		G	MA blah	MA blah	MA blah	700	6
11		H	MA blah	MA blah	MA blah	700	6
12		J	MB blah	MB blah	MB blah	800	9
13		C	WA blah	WA blah	WA blah	1000	10
14		F	WB blah	WB blah	WB blah	1000	10
15		I	MA blah	MA blah	MA blah	1200	12
16
17		Womens A
18		Team	Blah	Blah	Blah	Rank distance	LSD rank
19		A	WA blah	WA blah	WA blah	570	2
20		B	WA blah	WA blah	WA blah	540	1
21		C	WA blah	WA blah	WA blah	1000	3
22		etc	WA blah	WA blah	WA blah
23
24		Womens B
25		Team	Blah	Blah	Blah	Rank distance	LSD rank
26		D	WB blah	WB blah	WB blah	700	2
27		E	WB blah	WB blah	WB blah	600	1
28		F	WB blah	WB blah	WB blah	1000	3
29		etc	WB blah	WB blah	WB blah
30
31		Mens A
32		Team	Blah	Blah	Blah	Rank distance	LSD rank
33		G	MA blah	MA blah	MA blah	700	1
34		H	MA blah	MA blah	MA blah	700	1
35		I	MA blah	MA blah	MA blah	1200	3
36		etc	MA blah	MA blah	MA blah
37
38		Mens B
39		Team	Blah	Blah	Blah	Rank distance	LSD rank
40		J	MB blah	MB blah	MB blah	800	3
41		K	MB blah	MB blah	MB blah	580	2
42		L	MB blah	MB blah	MB blah	550	1
43		etc	MB blah	MB blah	MB blah
44
Sheet1

Cell Formulas
Range	Formula
B4:G15	B4	=LET(W,VSTACK(B19:F21,B26:F28),M,VSTACK(B33:F35,B40:F42),B,VSTACK(W,M),show,SORT(IF(Include="Men",M,IF(Include="Women",W,B)),5),dist,TAKE(show,,-1),HSTACK(show,BYROW(dist,LAMBDA(r,MATCH(r,dist,)))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Include	=Sheet1!$E$1	B4

Cells with Data Validation
Cell	Allow	Criteria
E1	List	Men, Women,Both

It's hard to work with pictures, so when you're posting screenshots, ideally you'd use our XL2BB add-in. That way we can simply click here and copy/paste into a workbook for testing:

Or if that's a step too far, paste in table format, with row and column headers written in, so we can see which cells are being used.

	B	C	D	E	F	G
1			Include	Both
2	Combined
3	Team	Blah	Blah	Blah	Rank distance	LSD rank
4	B	WA blah	WA blah	WA blah	540	1
5	L	MB blah	MB blah	MB blah	550	2
6	A	WA blah	WA blah	WA blah	570	3
7	K	MB blah	MB blah	MB blah	580	4
8	E	WB blah	WB blah	WB blah	600	5
9	D	WB blah	WB blah	WB blah	700	6
10	G	MA blah	MA blah	MA blah	700	6

And if you're posting code, please put it in code tags:

'So that your code looks like this
'and can be copied/pasted for us to test