So who can make a jersey number generator?

[andjbock]

One that generate random numbers in an excel file that stresses number 1-55 with a few (but not many #'s) exceeding 55?

If you can I'd love to see it.

[disposableheros]

please make it smart enough so that a number isnt on a team more than once. thank you.

[gubaru]

Something like this? There's a readme sheet but the gist is it will give you 2,500 random numbers with most results in the 1-55 range with a macro that will produce 25 unique results for a team.

*EDIT*
See post #7 for the file.

I want to say thank you to blueturf as I borrowed shamelessly from his origin maker spreadsheet to form the guts of this. I hope you don't mind.

[disposableheros]

Originally Posted by gubaru

Something like this? There's a readme sheet but the gist is it will give you 2,500 random numbers with most results in the 1-55 range with a macro that will produce 25 unique results for a team.

I want to say thank you to blueturf as I borrowed shamelessly from his origin maker spreadsheet to form the guts of this. I hope you don't mind.

thanks J

is there anything spreadsheet/database/programming/computer related you cant do?

[gubaru]

Originally Posted by disposableheros

thanks J

is there anything spreadsheet/database/programming/computer related you cant do?

Plenty but I appreciate the confidence.

[andjbock]

Good Times. Thanks.

[gubaru]

I played around with this a little more and modified it to use the macro for all processing and prompt for both the total jersey #s needed and the unique #s needed. If you don't want to generate unique #s, answer 0 to that question.

This should work a little better for leagues with more than 2500 players or a non-standard roster size. If you'd prefer not to use the macro, I attached a ...nm (no macro) version as well that only generates 2500 #s with no built-in option for unique numbers.

[Faroo6]

This is probably a stupid question but where do you paste it into the .csv file for ootp so that you get 25 unique numbers for each team.

[gubaru]

Originally Posted by Faroo6

This is probably a stupid question but where do you paste it into the .csv file for ootp so that you get 25 unique numbers for each team.

I included the unique numbers option in case people wanted random numbers to enter for a team using the editor. The reason is there's no easy way to do it for a team using just the players_general.csv file because it doesn't include team or roster information. If you have the BOSI exporter, you can use the players.csv file in conjunction with the players_general.csv file to come up with a solution. Here are the steps you need to take:

1. Export the players_general.csv file and change the extension to .txt if you're using Excel. This way you can set the column formats when Excel opens the file. Change the height column to Text or you lose all the heights of 6' 0".
2. Export the BOSI files and open players.csv.
3. Enter this formula in Cell V2 of players_general.csv: =VLOOKUP(A2,players.csv!$A:$EQ,147,FALSE) then copy and paste down the column. This will give you the team ID. Note that -1 means the player is a free agent.
4. Enter this Formula in Cell W2 of players_general.csv: =VLOOKUP(A2,players.csv!$A:$ER,148,FALSE) then copy and paste down the column. This will give you the level at which the player is playing.
5. Choose Data --> Sort. Use Column V as the 1st criterion and W for the second. It doesn't matter if it's ascending or descending.
6. Copy and paste the unique numbers into the section that has the team/level you want to assign them to.
7. Once you're done, delete columns V & W.
8. Save the file as a .csv format and import it into OotP.

[gubaru]

Since the above process is kind of a PITA, I reworked the file to include an option to automatically update the players_general.csv file with unique jersey numbers for all players on major league rosters. It renames the players_general.csv to a .txt file; so, you wont lose heights of 6' 0". For the process to work three things must be done:
* players_general.csv must be exported.
* players.csv must be exported using the BOSI exporter.
* you must know how many players are in the league.

The file is attached to post #7 above.

I ran through this a few times and it looks to be working correctly. If you see any problems or errors, let me know.

[disposableheros]

the new version of this mod is great, Jason. it makes the tedious task of redoing numbers a breeze. sure did come along way in a short time. Thanks a million.

a question....when you DO automatically generate unique # for each team, the last question asks, "Unique #". does this get answered true/false like the rest or can we put in an actual # of unique # we want for each team? ie. a 22-man Major league roster or filling the 40-man with unique #

a comment...you might want to make a note in the readme that says you have to reimport the players_general.csv to get the new numbers to show up

[gubaru]

Originally Posted by disposableheros

the new version of this mod is great, Jason. it makes the tedious task of redoing numbers a breeze. sure did come along way in a short time. Thanks a million.

a question....when you DO automatically generate unique # for each team, the last question asks, "Unique #". does this get answered true/false like the rest or can we put in an actual # of unique # we want for each team? ie. a 22-man Major league roster or filling the 40-man with unique #

a comment...you might want to make a note in the readme that says you have to reimport the players_general.csv to get the new numbers to show up

The unique # question refers to how many numbers you need for the players on the active roster, e.g. 25, 16, 30. I'll make a note about importing.

I'm tinkering a little more with this. So far, I've added some error handling to take care of common problems (players_general.csv open when it tries to rename it, etc.), consolidated the message boxes into one message box with some checkboxes and a couple text fields and I believe I've got everything working to allow the user to set their own weights for the numbers. I'm trying to work out a way to allow split ranges (1-25,33-40) and to assign unique numbers to players on all roster levels (A, AA, AAA and ML).

[gubaru]

I've made a couple more changes to this and the new version will be the last for a while. The changes are:

* While there was no code in the no macro version, I forgot to delete the module. I deleted it so that the macro message no longer shows up.
* Redesigned the interface to put everything on a couple forms instead of multiple message boxes.
* Allow custom weights to be set for the jersey #s. There are three weighting levels and split ranges are allowed.
* Assign unique number to all rosters (A, AA, AAA and ML).
* Check for extra commas in the name and/or origin fields and diplay the player causing the problem in a message box.

There is a readme sheet with instructions. Any questions or problems, let me know.

[andjbock]

If anyone has a problem with the heights not formatting properly (because they don't follow instructions, like me) this will help.

http://www.ootpdevelopments.com/boar...ad.php?t=70316