|Transferring Data Between Old and New START|
|Theory and Strategy - Platinum|
|Written by Todd Zola|
|Saturday, 05 January 2013 11:41|
There are actually three ways (short, medium, long ) to transfer the data, depending on your level of Excel acumen. At the end of the day, the exact same thing is accomplished, it is a matter of comfort. The short method assumes a basal level of Excel experience.
Please note it does not matter if you have changed the order of the players – this will work if you did.
1. Open both the old and new versions
2. Go to the HITTERS TAB on the OLD version
SHORT METHOD – Highlight columns A through C and copy. Come over to the NEW version and create a new worksheet and paste into cell A1
MEDIUM and LONG VERSION – Left click on the A at the top of Column A. While continuing to hold down the mouse button, draft over to Column C so A, B and C are all highlighted. Anywhere within that rectangle, right click then choose copy using the left click. Come over to the NEW version and click on the tab to the right of where it says ROSTERS at the very bottom. This should create a new worksheet that is called Sheet 1. Go to cell A1 and left click in it. Now either hit Ctrl V or right click and choose PASTE. You should see the data copied over from the OLD version.
3. Go to the PITCHERS TAB on the OLD version
SHORT METHOD – Highlight columns A through C and copy. Come over to the NEW version and paste into columns F through H of the new worksheet.
MEDIUM – Repeat what you just did except instead of pasting into cell A1, paste into cell F1
LONG VERSION – Left click on the A at the top of Column A. While continuing to hold down the mouse button, draft over to Column C so A, B and C are all highlighted. Anywhere within that rectangle, right click then choose copy using the left click. Come over to the NEW version and click cell F1 (you should still be in Sheet1). Now either hit Ctrl V or right click and choose PASTE. You should see the data copied over from the OLD version.
4. You are done with the OLD version so you can close it up. Don’t delete it in case something goes wrong. The rest of the instructions pertain to the NEW version.
5. On Sheet1
SHORT METHOD – Copy column C and insert into Column A so the names come first, then the team then the position. Repeat with column H into column F.
MEDIUM and LONG version – Left click on the C at the top of Column C then right click and choose CUT. Right click on the A on top of column A and choose INSERT CUT CELLS. This should put the names in A, the teams in B and the positions in C. Left click on the H at the top of Column H then right click and choose CUT. Right click on the F on top of column F and choose INSERT CUT CELLS. This should put the names in F, the teams in G and the positions in G.
6. Go to the HITTERS TAB
SHORT METHOD – using vlookup, transfer the team and positions. When you are done, copy then paste special. Do a find for #N/A on the team and positions columns and replace with a blank cell. Repeat for the PITCHERS TAB. Don’t worry about the 0 in the undrafted players cells, you can type over then when the player is drafted and they don’t impact anything. If you are familiar with the IFERROR function, you can use that instead of FIND/REPLACE.
MEDIUM and LONG METHOD – In cell A7 (under TM) type exactly what is below. You can also copy/paste it into the cell from here if you want.
In cell B7 (under POS), type exactly what is below or copy/paste it into B7:
If the player was drafted, you will see his team and position. If he has not been drafted, there will be zeroes in the cells. Anything other than that and there is an error.
Left click in A7, hold the mouse down, drag to B7 then drag down until you cover all the listed players and release. This should make a rectangle highlighting columns A and B. On your keyboard, hit the Ctrl button and D at the same time. This is fill down and it will populate all the cells with the data.
The rectangle should still be there, but if it isn’t, highlight the data again by left clicking in A7, dragging to B7 and then down.
Anywhere in that rectangle, right click and choose copy. The border should now “be moving”. Right click and choose paste special, then choose the radio button VALUES then click OK. You have now converted the vlookup function to text.
If you are using the latest version of Excel 2010, the paste special shows three icons – choose the leftmost option.
The rectangle should still be there, but if it isn’t regenerate it is. On your keyboard, hit the CTRL and F at the same time. In the FIND WHAT space, type in #N/A. Then click REPLACE and leave REPLACE WITH empty. Click in REPLACE ALL. You have now deleted all the error messages due to new players in the pool. This was necessary or the standings would not calculate.
6. Go to the PITCHERS TAB
SHORT METHOD – repeat using the pitchers data on Sheet1
MEDIUM AND LONG METHOD – Do EXACTLY what you just did, EXCEPT type or paste the following into cell A7
And in cell B7
You are done except for re-entering the team names on the setup page. These can be copied and pasted in directly from the old version if you wish.
Look at the standings and roster pages to confirm your data has transferred properly.
If you are having difficulties, we can arrange a technical support phone call or you can send your file to me and I will do the transfer.