Step by Step on how to do a roster in excel

Discussion in 'The Tradition' started by Hellisan, Nov 21, 2011.

  1. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590
    Twister18 this one's for you... Hopefully it makes sense and is easy to follow. Please try it at least once as this was a bear to type up.

    First of all you'll need to use this excel file that I made up which is called roster maker, I seem to name my files stupid shit like this and "schedule maker"

    I will upload it here.
     

    Attached Files:

    • Like Like x 1
  2. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590
    When you open the file you will notice there are 6 tabs down below. The main reason there are so many is that I wanted to add height and weight to all of this. When you export the roster file from the dynasty wire website it doesn't include that... But the rosters you see on the website have them. So we export the roster in excel but also cut and paste them in directly from there to get that height/weight info...

    To start, go to the dynasty wire website...

    http://www.easports.com/dynasty

    Login, navigate to T1, etc... Now click Roster. At the top right, click Download Roster. Then click to save file. Keep track of where you save this. Open that file with excel, highlight everything inside, and paste into cell A1 of the first tab in my spreadsheet (which is entitled "Paste Roster"). Everything we are doing now is to synch up the height and weight of the players from the roster here in a second. We need to sort this pasted data first by last name, then by OVR rating. Click the box in the top left of the sheet (left of "A" and above "1") to highlight all the data. Go to the data tab above and click sort. Check "my data has headers" and then for the first column we want to sort by LastName in order of A to Z. Click "Add level" to get a second condition... Then by Overall (largest to smallest). Now click O.k.

    You'll have your roster sorted alphabetically and if two players have the last name, it goes in order of highest OVR. This is how the dynasty wire sorts its data when you sort by name... Which we're going to do now. Go back to the website and the roster there. Click "name" above so that your players appear in alphabetical order (it sorts them so that if two players have the last name, it is the highest OVR one that comes first). Go to the very bottom of the page now, to your last player. Using your mouse go to the right of the last player's city and state and then left click, and drag up all the way to the top. highlight all of the player data, including images etc... for all players. right click and copy all that shit.

    Now go to the second tab in the spreadsheet entitled "paste copy"

    Right click in Cell A1, and click "paste special" - it gives you a popup box that says as: .... Highlight "text" then hit o.k. You'll notice that all that data is paste very strange, down the page. No worry, I've accounted for this.

    Now go to the tab called "unsorted roster" (TAB 3) Don't ever change anything on this page... All this is for, is to get the data and paste into "paste and sort" (TAB 4). You'll see that on Tab 3 it has the height and weight of all the players. If you find a mistake please tell me. Above we sorted by last name and OVR... I haven't come across a situation where there are two players with the same last name and OVR rating, I have no idea where it would sort from there. You might have an issue if that happened where the height/weight don't match up or aren't correct.

    Ok, so highlight the top left box to highlight everything on the page on TAB 3, and right click / copy it. Go to Tab 4. In Cell A1, right click. paste Special. click the "Values" circle under paste, and hit OK. If you have fewer than 70 players on your roster there will be at least 1 row with zeros all the way across at the bottom. , just delete these.

    Now still in the "paste and sort" (TAB 4) tab, we're gonna do a custom sort to get the players in order of position then OVR ranking. Highlight all the data on the page again (a shortcut for this is CTRL A, by the way) and go to Data, and click Sort. Check "my data has headers" and for the first one sort by position. Click "custom list". highlight the "new list" text under "custom lists:" and paste this following text into the "list entries" box:

    QB, HB, FB, TE, WR, LT, LG, C, RG, RT, LE, RE, DT, LOLB, MLB, ROLB, CB, FS, SS, K, P

    Keep in mind if you want to sort your shit in a different order you can, but you have to use those position names because that's how they're exported in the roster. Once you have pasted that line into the "list entries" box, hit "add" You now have this custom way of sorting the data by position. Hit "OK" to go back to the sort box and make sure that Position is being sorted in that order. Click "add level" and have that be OVR, largest to smallest. This will sort the roster by position then highest OVR. Obviously it will not be perfect, you may have to move players around later if that is not the order you want your depth chart displayed.

    Now once again highlight all that data and copy, and paste into the top left cell (A1) of Final Roster. This is where you will format everything into how you want it presented. I changed all the font to size 9, and I recommend doing so.

    You can look at the last tab (Final Roster example) to see how I have mine and basically use it as a sample or do something totally different.


    --------------------------------------------------------------------------------------------------------------------------------------------------------

    One you have pasted the result into "final roster" you will need to then format the roster to your liking. This is what will take the most time but you'll get fast at it. Here is how I do mine. Keep in mind you can do yours any way you like.

    Since this is so much data, you will want to shorten some things such as the year of the player... Highlight the F Column by clicking on the F above... CTRL F, click the replace tab, and then type Freshman in the first blank, FR in the second blank, then click "replace all" - Do that for sophomore / SO, Junior / JR, Senior / SR, and so on. It will keep the (RS) in the right instance. After you do that, hover your mouse over line between the "F" and the "G" (columns). You will see an icon with two arrows pop up, now double click. That will resize that column to your desired size. Do this whenever you need a wider or narrower column to fit the text you have.

    Personally I'm just deleting the Hometown column... I don't think anybody would care other than me, and it's too much data width wise to really justify keeping it in there. HIghlight the G Column by clicking on the G above, then right click and delete.

    I like everything centered in columns G through AY so just highlight those entire columns the same as you did before and hit the centered text button above.

    Now we're going to use the very top column to create a position header that we will use for every position. Highlight the top two cells (A1 and B1) and right click, format, and check Merge. Now Type in the position header name QUARTERBACK. Highlight the whole row by clicking the "1" on the left and make this bold by clicking the bold "B" above in the menus. You can also have a background color if you want, it's the box right next to the text color. This is all up to you this formatting shit.

    Now Click the 1 again on the left to highlight the first row. Now highlight whatever row (horizontal) your first halfback is in... Right click, and hit insert copied cells. This will give us a header for the halfback position. Now rename that header. Do this for every position as you see fit. Me personally I don't want a header for all the different O line or D line positions, I just do one header for each of those as well as linebackers. Now that you have that done, please notice that your depth chart may not be in the same order. You can re-order a position by cutting and inserting a certain player (row) above or below where he was. Highlight that row, right click, cut, go to where you want him, right click, insert cut cells... I like to have red text for the players that are redshirting, so I just highlight those rows and go to the text color above and select red.

    Here is the part that can get tedious but goes quickly if you know what you're doing... You don't have the space to include every rating for every player, and why would you want to? You have to manually delete all the ratings that don't matter for each position. Go through and only select the cell range that you want to delete (including the position header for that position) and just right click and delete the unneeded ratings Make sure it is shifting the text left and not up. You can click CTRL Y after you do this once to delete other ratings. So let's take QB... I want everything in there up until "RBK". If you don't care about running QB's, maybe delete all the running ratings... Up to you. The reason you have to do this every time is because of shifting roster sizes. If you always had the same sized roster and the same amount of players at a given position, I could do this all for you... Maybe there is an excel genius out there that could do this despite the ever changing roster sizes. BELIEVE ME THIS MIGHT SEEM TEDIOUS BUT ONCE YOU GET THE CTRL-Y THING DOWN THIS WILL TAKE YOU 2-3 MINUTES. Still much better than typing up a roster.

    Now I insert a comment box (for me to talk about each position) and a black divider line row. First find the longest ratings list you have... Mine is FB and TE, they do pretty much everything on offense like blocking... I want the ratings header above each position to be colored all the way across to the same length as this longest rating. So I highlight all the boxes for each position header and color them with the same grey background all the way across so it looks even. Next I insert two rows before the halfback header. THe first one is for the commentary box and hte second will be the black divider. For the comment row I highlight the entire thing, right click, and check the following: Merge... text wrap and make sure it is aligned to the left. This will allow you to type as much as you want, you'll just possibly have to resize the row vertically like I showed you above for the horizontal stuff. Now right click on it... Go to the border tab, and click the box for a line above the box... This will separate the ratings from your description text... now highlight the next (divider) row, and do a black background.

    Now you can just highlight those entire rows (both of them) copy, and insert before each subsequent position.

    -----------------------------------------------------------------------------------------------------------------------------

    You can do this in google docs and post it to the forum that way but I just like to do a JPEG. Highlight all the cells of your roster (only the cells, not the whole rows or columns) and copy.

    Go to an image editor such as photoshop or the free (and excellent) photofiltre

    http://majorgeeks.com/PhotoFiltre_d4587.html

    and edit / paste as new image, or with photoshop you have to create a new image or something then paste. SAve as a JPEG, upload to your favorite image host (I use imageshack) and then paste it as a normal photo.

    *sigh...* good luck.
     
    • Like Like x 1
  3. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590
    Here is what the final result looks like with height and weight now in the mix.

    [​IMG]
     
    • Like Like x 1
  4. Twister18

    Twister18 I aint got time to bleed!

    Joined:
    Oct 26, 2009
    Messages:
    4,134
    Helli, when you hit the A1 button to bring up paste special, I don't see text in the options. I am using 2007.
     
  5. jms493

    jms493 Veteran Member

    Joined:
    Feb 10, 2009
    Messages:
    8,906
    that is not what he is saying. I think you need to paste special "values" If a weird pop up appears, choose convert to text.
     
  6. Twister18

    Twister18 I aint got time to bleed!

    Joined:
    Oct 26, 2009
    Messages:
    4,134
    ok. Thanx
     
  7. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590
    Did that help? I wasn't sure what step you were on though... Let me know..
     
  8. KnightNoles

    KnightNoles Learn to Compete

    Joined:
    Jul 6, 2009
    Messages:
    19,823
    bomb diggity!
     
    • Like Like x 1
  9. Twister18

    Twister18 I aint got time to bleed!

    Joined:
    Oct 26, 2009
    Messages:
    4,134
    I may have to start over. I think I am lost myself. :p
     
    • Like Like x 1
  10. DelZaster

    DelZaster Walk On

    Joined:
    Oct 30, 2009
    Messages:
    1,477
    once you have completed all the above tasks and you know how to do it trade your NCAA disc, delete the file n play real Football instead LMAO
     
    • Like Like x 1
  11. KnightNoles

    KnightNoles Learn to Compete

    Joined:
    Jul 6, 2009
    Messages:
    19,823
    I saw a del post and was thinkin oh lawd this can't be good... My assumption was correct
     
  12. Keller

    Keller The enemy of my enemy is my friend.

    Joined:
    Apr 14, 2010
    Messages:
    13,939
    Hellisan - So in the case that we have a player with the same last name and same overall, whats the easiest solution for getting everyone's height and weight to match.

    I went through all the crap and have a 314lb QB, LOL!
     
  13. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590
    Oh good... tell me how the wire sorted those two guys and i will adjust the sort accordingly
     
  14. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590

    PS, you could always switch their names ;-)
     
  15. Keller

    Keller The enemy of my enemy is my friend.

    Joined:
    Apr 14, 2010
    Messages:
    13,939
    Yeah but I wasn't sure who got moved where and such. Literally just flip em and everything else is still correct?
     
  16. Hellisan

    Hellisan Schemin 'em up

    Joined:
    Feb 10, 2009
    Messages:
    10,590
    I would think so, right? I haven't fucked with that in awhile let me look at it...
     

Share This Page