Organizing a list of names week by week

[echeski1]

In my head this seems like a simple request of excel, but I am just not sure at all how to go about executing. Here is what I am looking to accomplish:

I am managing a recreational co-ed softball team and I want to be able to auto generate a roster each week based on who is attending, what position(s) each person can play, and making sure each player gets their fair share of time on the field.

We play 7 innings each game and can have 10 players on the field at a time, max 6 male and 4 female players. Our total team is 10 males and 6 females. There are some players who can play anywhere on the field and others who are only able to play 1 or 2 positions. Lastly, I want to make sure each player that attends get a fair share of fielding time. So, these are my sorting criteria. Given the following name list could someone help me create a template that can be easily edited each week?

Here are the names:
Male: Ryan
Walt
Michael
Jeff
Chris
Brett
Peter
Rob
Chris
Eric

Female: Laura
Julie
Alison
Sara
Nora
Rachel

Thanks so much for your help!

 

[elleg007]

Not so simple... This is really a multi-variable optimization problem. You have at least 6 variables to consider, including:

at a single time...
1) ensuring you have exactly 10 players on the field
2) ensuring you have no more than 6 males on the field
3) ensuring you have no more than 4 females on the field
4) ensuring you have one person assigned to each position
5) ensuring you have no more than one person assigned to a single position

etc.

Now, while this is something Excel can do, it's not exactly easy (at least, not in my experience, though there's likely someone out there smarter than me who would disagree).

Additionally, you aren't clear on what it is you want. First you say you want to "auto generate" a roster, then you say you want a "template". No offense, but a recreational softball team is a bit trivial for you or anyone else to spend a ton of time creating a tool to automatically generate some kind of rotating roster that meets all of your criteria. A template, however, is a bit more do-able.

I think I can provide you with a solution that would be a happy medium; while you'd still need to use the "guess and check" method to come up with your roster, the spreadsheet could provide useful feedback on what you plug in to help you make better decisions. And, if I wanted to get a bit fancy, it could prevent you from assigning someone to a position they can't play.

If I can figure out how to post a file, I'll post you an example.

 

[echeski1]

Not so simple... This is really a multi-variable optimization problem. You have at least 6 variables to consider, including:

at a single time...
1) ensuring you have exactly 10 players on the field
2) ensuring you have no more than 6 males on the field
3) ensuring you have no more than 4 females on the field
4) ensuring you have one person assigned to each position
5) ensuring you have no more than one person assigned to a single position

etc.

Now, while this is something Excel can do, it's not exactly easy (at least, not in my experience, though there's likely someone out there smarter than me who would disagree).

Additionally, you aren't clear on what it is you want. First you say you want to "auto generate" a roster, then you say you want a "template". No offense, but a recreational softball team is a bit trivial for you or anyone else to spend a ton of time creating a tool to automatically generate some kind of rotating roster that meets all of your criteria. A template, however, is a bit more do-able.

I think I can provide you with a solution that would be a happy medium; while you'd still need to use the "guess and check" method to come up with your roster, the spreadsheet could provide useful feedback on what you plug in to help you make better decisions. And, if I wanted to get a bit fancy, it could prevent you from assigning someone to a position they can't play.

If I can figure out how to post a file, I'll post you an example.

I guess it seemed easier in my head cause I could visualize exactly what I wanted. I appreciate you taking the time to comb through my gibberish and get at the heart of the problem.

I understand its a relatively trivial thing to spend time on, but as you mentioned there are a lot of people out there smarter than I, and I felt one of them might be able to generate something like this in a relatively short period of time.

I appreciate your time and effort.

 

[elleg007]

I understand its a relatively trivial thing to spend time on, but as you mentioned there are a lot of people out there smarter than I, and I felt one of them might be able to generate something like this in a relatively short period of time.

I understand, and please don't feel like I'm saying you shouldn't have asked. I'm just trying to clarify to you what it is exactly that you asked.

So, it appears I can't post files, so let's start with this. First step, copy the text in the "code" below into a simple text-editing program like Notepad:

Male,1,2,3,4,5,6,7,Player Total,,Player,Position 1,Position 2,Position 3,Position 4,Position 5,,,,,
Brett,Center Field,,,,,,,1,,Alison,Left Field,Center Field,Catcher,,,,,,,
Chris 1,Left Field,,,,,,,1,,Brett,Center Field,Left Field,Catcher,,,,,,,
Chris 2,Rover,,,,,,,1,,Chris 1,Left Field,1st Base,Catcher,,,,,,,
Eric,Catcher,Catcher,,,,,,2,,Chris 2,Rover,Center Field,Catcher,,,,,,,
Jeff,3rd Base,,,,,,,1,,Eric,Catcher,Right Field,,,,,,,,
Michael,2nd Base,,,,,,,1,,Jeff,3rd Base,2nd Base,Catcher,,,,,,,
Peter,Right Field,,,,,,,1,,Julie,2nd Base,1st Base,Catcher,,,,,,,
Rob,Shortstop,,,,,,,1,,Laura,Pitcher,1st Base,Catcher,,,,,,,
Ryan,Pitcher,,,,,,,1,,Michael,2nd Base,3rd Base,Catcher,,,,,,,
Walt,1st Base,,,,,,,1,,Nora,Rover,Center Field,Catcher,,,,,,,
Total Male,Too Many,1,0,0,0,0,0,,,Peter,Right Field,Catcher,,,,,,,,
Female,,,,,,,,,,Rachel,Catcher,Right Field,,,,,,,,
Alison,,Left Field,,,,,,1,,Rob,Shortstop,3rd Base,Catcher,,,,,,,
Julie,,2nd Base,,,,,,1,,Ryan,Pitcher,Shortstop,Catcher,,,,,,,
Laura,,Pitcher,,,,,,1,,Sara,Right Field,Catcher,,,,,,,,
Nora,,Rover,,,,,,1,,Walt,1st Base,Shortstop,Catcher
Rachel,,Catcher,,,,,,1,,,,,
Sara,,Right Field,,,,,,1,,,,,
Total Female,0,Too Many,0,0,0,0,0,,,,,,
Total Players,OK,Not Enough,Not Enough,Not Enough,Not Enough,Not Enough,Not Enough,,,,,,
Positions,,,,,,,,,,,,,
Pitcher,OK,OK,Missing,Missing,Missing,Missing,Missing,,,,,,
Catcher,OK,Too Many,Missing,Missing,Missing,Missing,Missing,,,,,,
1st Base,OK,Missing,Missing,Missing,Missing,Missing,Missing,,,,,,
2nd Base,OK,OK,Missing,Missing,Missing,Missing,Missing,,,,,,
3rd Base,OK,Missing,Missing,Missing,Missing,Missing,Missing,,,,,,
Shortstop,OK,Missing,Missing,Missing,Missing,Missing,Missing,,,,,,
Left Field,OK,OK,Missing,Missing,Missing,Missing,Missing,,,,,,
Center Field,OK,Missing,Missing,Missing,Missing,Missing,Missing,,,,,,
Right Field,OK,OK,Missing,Missing,Missing,Missing,Missing,,,,,,
Rover,OK,OK,Missing,Missing,Missing,Missing,Missing,,,,,,




Player,Position 1,Count,Position 2,Count,Position 3,Count,Position 4,Count,Position 5,Count
Alison,Left Field,1,Center Field,0,Catcher,0,,0,,0
Brett,Center Field,0,Left Field,0,Catcher,0,,0,,0
Chris1,Left Field,0,1st Base,0,Catcher,0,,0,,0
Chris2,Rover,1,Center Field,0,Catcher,0,,0,,0
Eric,Catcher,1,Right Field,0,,0,,0,,0
Jeff,3rd Base,0,2nd Base,0,Catcher,0,,0,,0
Julie,2nd Base,0,1st Base,0,Catcher,0,,1,,0
Laura,Pitcher,0,1st Base,0,Catcher,0,,0,,0
Michael,2nd Base,0,3rd Base,0,Catcher,0,,0,,0
Nora,Rover,0,Center Field,0,Catcher,0,,0,,0
Peter,Right Field,0,Catcher,0,,0,,0,,0
Rachel,Catcher,0,Right Field,0,,0,,0,,0
Rob,Shortstop,0,3rd Base,0,Catcher,0,,0,,0
Ryan,Pitcher,0,Shortstop,0,Catcher,0,,0,,0
Sara,Right Field,0,Catcher,0,,0,,0,,0
Walt,1st Base,0,Shortstop,0,Catcher,0,,0,,0

Step 2: Save the text file as Roster.csv
Step 3: Open the text file in Excel. This will give you the basic layout of the spreadsheet I can describe to you, but without the formatting and formulas.

 

[elleg007]

If I'm able, I'll post a picture for you tomorrow of what the spreadsheet looks like on my end with formatting and all that good stuff.

Anyway...
In column A (Rows 1 to 32, in my example) of that file, all names in this list need to be unique (thus, 'Chris 1' and 'Chris 2'), even if one is male and one is female; this includes names of positions.

Ignore rows 37 & up, and columns K and beyond for now, I'll come back to them later (tomorrow).

Columns A to H, Rows 2 to 11 and 14 to 19 are your rosters. I decided on 7 rosters kind of at random. I figured if you wanted to rotate positions each game, by inning, this would provide that schedule.

In Row 12, replace cell B12's value of 'Too Many', with: =IF(COUNTA(B2:B11)>6,"Too Many",COUNTA(B2:B11))

Drag that formula to the right, through column H.

In row 20, replace cell B20's value of '0' with:
=IF(COUNTA(B14:B19)>4,"Too Many",COUNTA(B14:B19))

In row 21, replace cell B21's value of 'OK' with:
=IF(COUNTA(B2:B11)+COUNTA(B14:B19)<10,"Not Enough",IF(COUNTA(B2:B11)+COUNTA(B14:B19)=10,"OK","Too Many"))

In row 23, replace cell B23's value of 'OK' with:
=IF(COUNTIF(B$2:B$19,$A23)=0, "Missing",IF(COUNTIF(B$2:B$19,$A23)=1,"OK","Too Many"))

Drag that formula down through row 32, and right through column H to fill out the 'Positions' section.

In column 'I', replace cell I2's value of '1' with:
=COUNTA(B2:H2)

Drag this formula down through row 11, then copy one of those cells to I14, and drag it down through I19.

This gives you the basics... Hopefully you can see the purpose of all these formulas once you fill them out.

I can explain more tomorrow, including the rest of the spreadsheet.

 

[echeski1]

This is great so far! I completely understand where you are going with this and I really appreciate the effort you have put into it. Thanks so much! Anxious to learn more tomorrow. Have a great night!

 

[elleg007]

Here's a screenshot of what the spreadsheet looked like with formatting, etc.:
http://www.flickr.com/photos/63059555@N08/5734170581

I'm tied up in meetings today, so I can't post much more than that right now.

 

[echeski1]

Here's a screenshot of what the spreadsheet looked like with formatting, etc.:
http://www.flickr.com/photos/63059555@N08/5734170581

I'm tied up in meetings today, so I can't post much more than that right now.

It's ok, I appreciate you taking the time. Whenever you get a chance to help explain the other portions of the Spreadsheet let me know. Thanks!