Soccer Team Generator

[Legacy 396030]

Hello all,

I will be starting a soccer league for kids soon and was wondering if the teams could be generated based on years of experience and skill level so the teams will be equally matched. Thank you very much in advance for any help. I am looking forward to having some fun this spring with the kids! Oh forgot to mention I think there will be about 120 kids so there might be 12 teams. I only show 8 here.

I have a sheet setup like below:

Sheet2

*

A

B

C

D

E

F

G

H

I

J

K

L

M

3

Last Name

First Name

Phone Number

Years of Experience

Skill Level

Team 1

Team 2

Team 3

Team 4

Team 5

Team 6

Team 7

Team 8

4

Smith

0

888-555-1234

3

1

*

*

*

*

*

*

*

*

5

Jones

0

888-555-4321

3

1

*

*

*

*

*

*

*

*

6

Harvey

0

888-555-3333

2

2

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:122px;"><col style="width:122px;"><col style="width:122px;"><col style="width:142.67px;"><col style="width:80.67px;"><col style="width:68.67px;"><col style="width:68.67px;"><col style="width:68.67px;"><col style="width:68.67px;"><col style="width:68.67px;"><col style="width:68.67px;"><col style="width:68.67px;"><col style="width:68.67px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A4 {=IFERROR(INDEX(Sheet1!A$4:A$150,MATCH($C4,Sheet1!$F$4:$F$150,0)),"")} B4 {=IFERROR(INDEX(Sheet1!B$4:B$150,MATCH($C4,Sheet1!$F$4:$F$150,0)),"")} A5 {=IFERROR(INDEX(Sheet1!A$4:A$150,MATCH($C5,Sheet1!$F$4:$F$150,0)),"")} B5 {=IFERROR(INDEX(Sheet1!B$4:B$150,MATCH($C5,Sheet1!$F$4:$F$150,0)),"")} A6 {=IFERROR(INDEX(Sheet1!A$4:A$150,MATCH($C6,Sheet1!$F$4:$F$150,0)),"")} B6 {=IFERROR(INDEX(Sheet1!B$4:B$150,MATCH($C6,Sheet1!$F$4:$F$150,0)),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Eric W]

I had to think about it, but this rang a bell. I wrote a macro about a year ago that essentially does what you ask. Here's the link:

https://www.mrexcel.com/forum/excel...tions-randomly-select-teams-list-players.html

If that works mostly as you want, I can tweak it a bit for you, like I can raise the number of teams from 10 to 12, or change the sheet layout. Let me know what you think.

 

[Legacy 396030]

Thank you very much Eric I will try that out in the morning. You are a tremendous help!