Sort 6 teams by their first player

[chuckf201]

I have 6 teams that should formatted in 2 ways. First by players(3-4) in a team and then sort all teams by HCP (handicap) of player #1 using VBA.
Here is the tables in question:

Team 1​ HCP​ Dist​ Team 2​ HCP​ Dist​ Team 3​ HCP​ Dist​ Team 4​ HCP​ Dist​ Team 5​ HCP​ Dist​ Team 6​ HCP​ Dist​ KRAVITZ​ 0.630​ 1.646​ JUDD​ 0.581​ 2.841​ ANDERSON​ 0.588​ 3.149​ STARKEY​ 0.489​ 3.169​ MAHONEY​ 0.587​ 1.368​ KER​ 0.728​ 1.028​ FINGERMAN​ 0.505​ 3.119​ BARR​ 0.489​ 6.053​ MCKINZIE​ 0.543​ 3.957​ LYNCH​ 0.467​ 3.193​ JOHNSON​ 0.565​ 1.398​ ELLESTAD​ 0.527​ 1.314​ BYRD​ 0.323​ 1.366​ RALPH​ 0.430​ 3.445​ SMITH​ 0.419​ 2.483​ LEWIS-G​ 0.462​ 3.186​ HEARN​ 0.347​ 1.236​ MIERZWIAK​ 0.290​ 0.999​

looking at the teams, team 6 should be list first followed by Ker(.728) followed by Kravitz(.630) followed by Anderson(.588) followed by Mahoney(.587) followed by Judd(581) followed by Starkey(.489).
Sometime we have 4 man teams.
I've tried 2 nested for loops by doesn't quite do what I want.
I have Office365

Thanks.

 

[StephenCrump]

Like this perhaps?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Team 1	HCP	Dist	Team 2	HCP	Dist	Team 3	HCP	Dist	Team 4	HCP	Dist	Team 5	HCP	Dist	Team 6	HCP	Dist
2	KRAVITZ	0.630	1.646	JUDD	0.581	2.841	ANDERSON	0.588	3.149	STARKEY	0.489	3.169	MAHONEY	0.587	1.368	KER	0.728	1.028
3	FINGERMAN	0.505	3.119	BARR	0.489	6.053	MCKINZIE	0.543	3.957	LYNCH	0.467	3.193	JOHNSON	0.565	1.398	ELLESTAD	0.527	1.314
4	BYRD	0.323	1.366	RALPH	0.430	3.445	SMITH	0.419	2.483	LEWIS-G	0.462	3.186	HEARN	0.347	1.236	MIERZWIAK	0.290	0.999
5
6
7	Team 6	HCP	Dist	Team 1	HCP	Dist	Team 3	HCP	Dist	Team 5	HCP	Dist	Team 2	HCP	Dist	Team 4	HCP	Dist
8	KER	0.728	1.028	KRAVITZ	0.630	1.646	ANDERSON	0.588	3.149	MAHONEY	0.587	1.368	JUDD	0.581	2.841	STARKEY	0.489	3.169
9	ELLESTAD	0.527	1.314	FINGERMAN	0.505	3.119	MCKINZIE	0.543	3.957	JOHNSON	0.565	1.398	BARR	0.489	6.053	LYNCH	0.467	3.193
10	MIERZWIAK	0.290	0.999	BYRD	0.323	1.366	SMITH	0.419	2.483	HEARN	0.347	1.236	RALPH	0.430	3.445	LEWIS-G	0.462	3.186
Sheet1

Cell Formulas
Range	Formula
A7:R10	A7	=SORTBY(A1:R4,INDEX(A2:R2,CEILING(SEQUENCE(,COLUMNS(A2:R2)),3)-1),-1)
Dynamic array formulas.

 

[chuckf201]

Like this perhaps?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Team 1	HCP	Dist	Team 2	HCP	Dist	Team 3	HCP	Dist	Team 4	HCP	Dist	Team 5	HCP	Dist	Team 6	HCP	Dist
2	KRAVITZ	0.630	1.646	JUDD	0.581	2.841	ANDERSON	0.588	3.149	STARKEY	0.489	3.169	MAHONEY	0.587	1.368	KER	0.728	1.028
3	FINGERMAN	0.505	3.119	BARR	0.489	6.053	MCKINZIE	0.543	3.957	LYNCH	0.467	3.193	JOHNSON	0.565	1.398	ELLESTAD	0.527	1.314
4	BYRD	0.323	1.366	RALPH	0.430	3.445	SMITH	0.419	2.483	LEWIS-G	0.462	3.186	HEARN	0.347	1.236	MIERZWIAK	0.290	0.999
5
6
7	Team 6	HCP	Dist	Team 1	HCP	Dist	Team 3	HCP	Dist	Team 5	HCP	Dist	Team 2	HCP	Dist	Team 4	HCP	Dist
8	KER	0.728	1.028	KRAVITZ	0.630	1.646	ANDERSON	0.588	3.149	MAHONEY	0.587	1.368	JUDD	0.581	2.841	STARKEY	0.489	3.169
9	ELLESTAD	0.527	1.314	FINGERMAN	0.505	3.119	MCKINZIE	0.543	3.957	JOHNSON	0.565	1.398	BARR	0.489	6.053	LYNCH	0.467	3.193
10	MIERZWIAK	0.290	0.999	BYRD	0.323	1.366	SMITH	0.419	2.483	HEARN	0.347	1.236	RALPH	0.430	3.445	LEWIS-G	0.462	3.186
Sheet1

Cell Formulas
Range	Formula
A7:R10	A7	=SORTBY(A1:R4,INDEX(A2:R2,CEILING(SEQUENCE(,COLUMNS(A2:R2)),3)-1),-1)
Dynamic array formulas.

I like the approach but my sort seems off. Also, the teams will be 3 or 4 players and the cells are populated with VB, wich is not a big deal.

Team 1 HCP Dist Team 2 HCP Dist Team 3 HCP Dist Team 4 HCP Dist Team 5 HCP Dist Team 6 HCP Dist
2 KRAVITZ 0.63 1.646 JUDD 0.581 2.841 ANDERSON 0.588 3.149 STARKEY 0.489 3.169 MAHONEY 0.587 1.368 KER 0.728 1.028
3 FINGERMAN 0.505 3.119 BARR 0.489 6.053 MCKINZIE 0.543 3.957 LYNCH 0.467 3.193 JOHNSON 0.565 1.398 ELLESTAD 0.527 1.314
4 BYRD 0.323 1.366 RALPH 0.43 3.445 SMITH 0.419 2.483 LEWIS-G 0.462 3.186 HEARN 0.347 1.236 MIERZWIAK 0.29 0.999


Dist Team 4 HCP Dist Team 5 HCP 0 Team 1 HCP Dist Team 6 HCP Dist Team 2 HCP Dist Team 3 HCP
3.149 STARKEY 0.489 3.169 MAHONEY 0.587 2 KRAVITZ 0.63 1.368 KER 0.728 1.646 JUDD 0.581 2.841 ANDERSON 0.588
3.957 LYNCH 0.467 3.193 JOHNSON 0.565 3 FINGERMAN 0.505 1.398 ELLESTAD 0.527 3.119 BARR 0.489 6.053 MCKINZIE 0.543
2.483 LEWIS-G 0.462 3.186 HEARN 0.347 4 BYRD 0.323 1.236 MIERZWIAK 0.29 1.366 RALPH 0.43 3.445 SMITH 0.419

 

[StephenCrump]

I like the approach but my sort seems off.

Your formula points to columns A:R, but your data is in columns B:S.

Also, the teams will be 3 or 4 players ...

My data was in A1:R4. If there were 4 in a team, I'd change R4 to R5.

=SORTBY(A1:R4,INDEX(A2:R2,CEILING(SEQUENCE(,COLUMNS(A2:R2)),3)-1),-1)

 

[chuckf201]

Your formula points to columns A:R, but your data is in columns B:S.

My data was in A1:R4. If there were 4 in a team, I'd change R4 to R5.

=SORTBY(A1:R4,INDEX(A2:R2,CEILING(SEQUENCE(,COLUMNS(A2:R2)),3)-1),-1)

My mistake. Now I need to learn what you did and why. My immediate MO is to go to VB.
Thanks for your help. we can close this.

 

[Peter_SSs]

Thanks for your help. we can close this.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.