Jump to content
Sign in to follow this  
Mike MacGregor

** Q&A: Importing Other Projections **

Recommended Posts

From another post, plus I've received this question a few times:

 

   A              B     C    D
1  Name           PaYd  PaTD RuYd
2  Manning,Peyton 4,225 29   90
3  Warner,Kurt    4,500 34   60

 

In the Compiler, lets input these in the section Site B. Go to the qb tab, cell CP5. This is the top line on the QB tab under the column PaYD. The formula in this cell should be (using our example):

 

=VLOOKUP($C5,Sheet1!$A$2:$D$3,2,FALSE)

 

> $C5 is finding the player name in the Compiler the formula will look for in the new projection table

> Sheet1!$A$2:$D$3 is the table reference for the new projections. This should be sorted by player name.

> 2 is the column number we want to pull data from in the new projections table, 2 being PaYd.

> FALSE makes sure the VLOOKUP formula looks for an exact match in the name. This makes sure it doesn't grab projections from the wrong player if he isn't in the list or if the name is spelled wrong.

 

Now, once this formula is in place, you can copy it across to the other stat categories in Site B, but you'll have to go back and change the '2' to the appropriate column reference for each stat category. From our example, the formula for PaTD should be:

 

=VLOOKUP($C5,Sheet1!$A$2:$D$3,3,FALSE)

 

and for RuYD:

 

=VLOOKUP($C5,Sheet1!$A$2:$D$3,4,FALSE)

 

...because these are the column numbers we find these stats in our new projections table.

 

At this point, we have the proper formulas in the top row of the Compiler. Now copy these formulas down through Site B. It should start picking up stats for all players it finds in the new projections table. For every player where there is not an exact match of player names, it will return #N/A, which is ok.

 

Now, do some checking that the stats came over properly. At this point we don't want those formulas in there anymore. Highlight the entire Site B stats. Choose Edit > Copy or right-click Copy. Without moving the cursor anywhere, we are going to copy the same thing on top of itself, but paste it as values to get rid of the formulas. Choose Edit > Paste Special.. As Values.

 

I know this seems long but once you get the hang of using VLOOKUP, it really isn't so bad. If the games column is not filled in, then insert 16 for the top player and copy it down for all other players. Also delete all the cells showing the #N/A because they had no match.

 

Finally, you have projections in Site B without typing them all in.

 

Whew! I'm tired. I'll be back with more soon.

 

[ 07-30-2003: Message edited by: Mike FF Today ]

Share this post


Link to post
Share on other sites

Formatting Player Names

 

From above, I skipped over the part about getting player names to the same format between the new projection table and the Compiler, but this is a crucial step if they are not already conveniently the same (unlikely).

 

If you copy projections off a website, then they can probably be in a few different forms, like:

 

  Column A            Column B
1 Manning,Peyton
2 Manning, Peyton
3 Peyton Manning
4 Peyton              Manning
5 Manning             Peyton
6 P. Manning

 

The first one in the list is great because that is the way names are inputted in the Compiler. Any of the other ones we need to do some work.

 

1. Edit > Replace

 

In number 2 in the list above, the First Name and Last Name are in the right order, separated by a comma, but that space in there will throw us off. Remember, it has to be exact.

 

We can quickly fix this though. Just highlight all of the names in your new projections table, choose Edit > Replace from the top menu. In the "Find" box just put one space " ". In the "Replace" box, don't put anything. Hit the "Replace All" button and it should quickly get rid of those spaces in the cells that were highlighted.

 

One thing to be careful of is of players with more than just that space before the comma. If they have 3 names (Randle El, Antwaan) then the replace will slam the Randle and the El together (RandleEl) which isn't right at all. Just watch out for things like that, but it is pretty rare.

 

2. Data > Text to Columns..

 

Example 3, Peyton Manning, is no good to us because although the name is in a single cell (good), the First Name and Last Name are in the wrong order. This one is going to take a couple of steps.

 

First, we need to split apart the name into 2 columns. Insert a blank column beside the player names column. Then highlight all of the player names and choose Data > Text to Columns.. from the menu.

 

At this point it gives you two choices, Delimited and Fixed Width. We want Delimited. Choose this and hit "Next". Since we have a space between the First and Last Name, that is the delimiter we want. Choose the "space" checkbox. You should see an example of the data splitting into 2 columns.

 

Hit "Finish" and now the names should be in 2 columns. Still not what we are looking for but we're getting there. Which leads me to...

 

3. CONCATENATE function

 

What this does is combines cells, text, etc. into a single cell. Do you see where this is going? We're going to combine the cell holding the players Last Name, a comma and the cell holding the players First Name.

 

Say we now have the following:

 

   A       B       C
1  Peyton  Manning  

 

In our example, we're going to put the following formula in cell C1:

 

=CONCATENATE(B1,",",A1)

 

This will result in Manning,Peyton, which is cell B1 + a comma + cell A1.

 

Copy this formula down for all players. Now, similar to our first post, we want to eliminate the formulas and just have the values. Highlight and copy the player names created from the CONCATENATE functions. Then in exactly the same spot, choose Paste Special.. As Values.

 

At this point you're effectively turned Peyton Manning into Manning,Peyton and are ready to apply the VLOOKUP functions.

 

In the examples above, the names that are already in 2 columns you won't have to worry about splitting the name. Just apply the CONCATENATE concepts and you should be good to go.

 

4. P. Manning

 

This one is a problem. Obviously no Excel functions or features are going to fill in missing characters in a name. But, if you have a situation like this, maybe you don't want to do VLOOKUP at all but rather use copy and paste. Let me give some hints and warnings on potential traps with that in my next post.

Share this post


Link to post
Share on other sites

2. Copy & Paste, Drag-and-Drop

 

Ok, lets look at a completely different way of getting projections in the Compiler, again without retyping them.

 

You have your table of new projections in a blank sheet. What we are going to do is copy them into the Compiler. Wait a second though, because there are some precautions/steps that need to be taken or we're sure to screw up the formulas that calculate the FF Pts.

 

First, the projections we are going to copy in should be in the same order as they will be in the Compiler. That is, for QB it should:

 

Games PaComp PaAtt Comp% PaYd PaTD PaINT RuAtt RuYd RuTD FumLost

 

Plus the player name will be in the first column. If the projections you copied don't include some stats (like Games, or PaComp), then include a blank column in your new projections where those stats would otherwise be.

 

It is almost time to copy them over. First though, we want to insert a blank column beside where we are putting the new projections in the Compiler to temporarily hold each players name. This will help us line up the correct projections with the correct player in the Compiler. Say we are inserting QB projections in Site B of the Compiler. Insert a blank column in column CL (highlight CL and right click Insert).

 

Now we're ready to copy the new projections over. Highlight the new projections, choose Edit > Copy and go to the Compiler. Place the cursor on the top left corner of the space where the new projections will go. Try to keep the formatting of the numbers by choosing Edit > Paste Special.. As Values rather than a straight paste.

 

The new projections are in there now, but they probably aren't lined up with the correct players as shown on the left of the screen. Time to use drag-and-drop.

 

For each player highlight there name and new projections under Site B. Then place your mouse cursor near the edge of the box until an arrow appears. When this happens, hold down the mouse button and you can move (drag) the cells to a new spot. Line it up with the player name from the Compiler and release the mouse button to drop it in place.

 

Think of this as a puzzle. You might need to move some players out of the way before you can move new players in the right place. Or, if some players are in the same order, you can move a bunch at one time.

 

The biggest precaution with drag-and-drop is it will mess up the formulas. What I suggest is to never drag-and-drop anything in or out of the top player row. That way the formulas in that top row should never be disrupted. Once all of your other drag-and-drop is done, copy all the formulas in row 5 from column I all the way to column AH down over all the other players. That should correct any problems that occured from drag-and-drop.

Share this post


Link to post
Share on other sites

Bump. A lot of people still asking about this.

Share this post


Link to post
Share on other sites
Originally posted by Mike MacGregor:

Formatting Player Names

 

...choose Edit > Replace from the top menu. In the "Find" box just put one space " ". In the "Replace" box, don't put anything. Hit the "Replace All" button and it should quickly get rid of those spaces in the cells that were highlighted.

 

One thing to be careful of is of players with more than just that space before the comma. If they have 3 names (Randle El, Antwaan) then the replace will slam the Randle and the El together (RandleEl) which isn't right at all. Just watch out for things like that, but it is pretty rare.

 

One tip to fix this is to search for ", " and replace with ",".

Share this post


Link to post
Share on other sites

In my compiler, the names are first last (Kurt Warner), not last,first (Warner,Kurt). Am i missing something?

Share this post


Link to post
Share on other sites

No, you're right. When I originally wrote this the names in the Compiler were Last,First. Since then I changed them to First Last because it worked with Buddy better and I think it made most people's attempts to import other stats easier.

 

All the concepts I've descibed above still have some merit depending on what you're trying to accomplish. Just pick and choose the tools that you need.

Share this post


Link to post
Share on other sites

Bumping (and thanking Mike K. for not pruning) :)

 

This is where I turn all of you into Excel experts ;)

Share this post


Link to post
Share on other sites

not sure if I am doing this right, but here's how I imported projections

 

First, I sorted each position by first name in the compiler... (aka, AJ Feely was the first QB, followed by Aaron Brooks, etc...)

 

Next, I started a new excel doc and pasted the projections from the other site into it. Then I sorted those by first name as well

 

Next, I copied each list of players in alphabetical order from the compiler, and pasted it next to the imported projections on the new worksheet. I also included the column order the stats appear in the 'site b" and "site c" sections of hte compiler

 

I then added columns and cut and pasted so that the imported stats would mirror thier apeparence in the compiler.

 

Next, I added rows where the imported projections did not match up with the compilers -- the compiler for example had more QBs than the import projections -- i simply made sure the names from the imported list lined up with the names on the compiler, and that names that were in the compiler but not in the import list had blank cells on the import projection list

 

all you have to do then is copy the projected stats, and paste the values only into the "Site B" and "Site C" areas on the compiler

 

Be careful not to screw around with the % weight the compiler gives each site's stats on the position pages -- you should do this from the "input" worksheet at the bottom, as on the position worksheets, the cells that say, for example "100% FFToday" are referenced to to those input cells -- you can set up to 3 ways to slice the data -- very cool

 

I would appreciate it if anyone can post where they can find free projections on other websites -- I know ESPN generally does this at some point, but they havent yet

 

cheers and good luck!

Share this post


Link to post
Share on other sites

Mike,

 

Thanks for the detailed instructions on the VLOOKUP functionality. I started to use some of the other tricks that I knew, but was wondering how much work it was going to be as projections get revised this summer.

 

Also...I'm trying to come up with a clever way to re-calculate auction values of the players based on the available cash pool once two keepers (and their associated cap value) have been identified on July 31st.

 

In addition, I'm trying to come up with a auction-value tiering method that you could use. For instance in a league with $12,000 in available auction dollars, how to allocate 36% in the first round, 23% in the second round, etc. I've got three years of auction history with my league and have determined auction-tiers that need to be incorporated in my auction strategy. I'll let you know what I come up with....

 

Awesome tool...Thanks for the Excel 97 fix.

 

:cool:

Share this post


Link to post
Share on other sites

Hey ICETIME. Thanks for the compliment. How about just leaving all the players in there, calculating the auction values, and then adding the keepers through Buddy, inserting the salary cost? Then you can compare who over/underpaid based on your results, and see what each team has left before the draft.

 

This is similar to how I tell other people to handle keepers. Leave the players in the Compiler for the ranking calcs. If they are removed, then the value calcs could likely get screwed up.

 

Even for an auction setup the Compiler creates position tiers. The tiers are calculated based on fantasy points. No matter what you spend on who, it is the fantasy points that count in the end.

 

Would this not be a better way to look at it than allocating dollars to a particular round of the draft? I could spend $40 to get a tier 3 RB and a bottom tier WR, or use that $40 for a tier 5 RB and a tier 3 WR. Which is better would be a judgement call.

 

Speaking of auctions and your experience, what do you think of the auction values in the Compiler? After discussing this with a few people who have extensive auction experience, I thought the method was pretty solid. But, not being much of an auction guy myself, I would always love more feedback to ensure the values are not too low ball or over the top.

 

[ 06-26-2003: Message edited by: Mike MacGregor ]

Share this post


Link to post
Share on other sites

snitwitch - Sorry I missed your post. That sounds like a solid method for getting other projections into the Compiler. Couple points:

 

1. What you could do is just copy the player names for each position from the Compiler into a new workbook. Then drop in your other projections, with player names, right beside them in this new workbook. Now drag-and-drop until the names line up. This will help make sure you do have players lined up with the right projections.

 

2. When you copy the data back into the Compiler, use Edit > Paste Special.. > As Values rather than just a straight paste. Just keeps the formatting in place which looks sharper.

Share this post


Link to post
Share on other sites

Mike-

 

When importing stats, I've found that some sites do not rate all of the players and consequently, the 2003 projections are returned as #N/A. Is there anyway to get the #N/A's in the section for Sites B and C to read 0 instead so that the overall projections still work? I tried the IF function in Excel, but I don't really understand how it works. Let me know. Thanks.

 

-M.

Share this post


Link to post
Share on other sites

Well, it's been five minutes since I asked the previous question and I found a help file in Excel that answered the question. I had to combine the IF and ISERROR functions. Using the example in the first post, my resulting function looked like this:

=IF(ISERROR(VLOOKUP($C5,Sheet1!$A$2:$D$3,2,FALSE)),0,VLOOKUP($C5,Sheet1!$A$2:$D$

,2,FALSE))

 

Hopefully that will help anyone who was having the same question I was.

 

-M.

Share this post


Link to post
Share on other sites

Hi SmallRugMountain. I feel I'm pretty good at the response time, but not necessarily 5 minutes good. (Had the in-laws over for dinner, or I would have been here!)

 

Anyway, here is another way that I use. Just insert your straight VLOOKUP formulas as you did the first time. Yes, it will produce a bunch of players with #N/A as a result because they cannot be found in the list you are pulling projections from.

 

Ok, next step once you are happy all the projections appear in the Compiler is to highlight the whole set of projections, choose Copy, and then Paste Special.. > As Values, right in the same spot. This will remove the formulas and keep the data that was pulled.

 

Now, while that whole area is highlighted, choose Edit > Replace, and we are going to remove all the #N/A cells. Just put #N/A in the "find" field and leave the "replace" field blank - not a space, just nothing. Ta-da! Just the projections are in. If the Compiler reads a blank, it should not give you the calculation errors.

 

Your method is solid too. A more complicated forumla but fewer steps involved.

Share this post


Link to post
Share on other sites

Man you guys are getting way to technical for me.

 

Oh well...

 

After reading half way down my mind is gone.

 

Mike : FYI - even though I understand about half of what you said. I think you are doing great with all these support question.

 

$10.95 is worth it, for all the time I will be bugging you later once I get started.

Share this post


Link to post
Share on other sites

Yes, I go a bit overboard sometimes. Even gets me :confused: from time to time with all the cell references flying around.

 

But thanks for the compliment. Don't worry about bugging me. That is what I'm here for ;)

Share this post


Link to post
Share on other sites

Bump as I'm getting lots of emails on this.

Share this post


Link to post
Share on other sites

I think we need one of them sticky things.

Share this post


Link to post
Share on other sites

I think I learned more about excel in this post, let alone one of my college computer classes all together!!! I think I might demand my money back!!

 

;)

Share this post


Link to post
Share on other sites

I am very close to getting the VLOOKUP to work, but I get the value from the line above what it should be!!

 

Any Ideas???

Share this post


Link to post
Share on other sites

Anyway to import more than just two other site's projections(other than entering them over a previous year's stats for example)?

 

Also, why is the inputs tab protected? I think I could probably fix the above problem here, with a little tinkering of the fomulas.

 

Thanks!

 

BTW, everyone laughed at me for bringing my laptop to the draft last year. But after my draft, using the compiler and draft buddy, they weren't laughing anymore...especially after I went to the playoffs in my rookie season in a supposedly tough league of FF veterans! :) ;) :eek:

Share this post


Link to post
Share on other sites

Just a word of caution - when importing other data and editing the names watch out for the rookie designations in the cheat sheet. That is the "r" with a circle around it, it is part of the name and hence has to exactly match when using the vlookup command.

 

[ 07-28-2003: Message edited by: Soufle ]

Share this post


Link to post
Share on other sites
Originally posted by usnrskc:

I am very close to getting the VLOOKUP to work, but I get the value from the line above what it should be!!

 

Any Ideas???

 

I'm guessing the reference you are looking up is actually the player one above the line where you have the formula set. This is the first part of the VLOOKUP.

 

If the player row is 5, you should have the cell on row 5 to input the formula, and the VLOOKUP should be:

 

=VLOOKUP(C5,[table to lookup data from],[column number for particular stat],0)

 

If you instead had the formula in a cell in row 6, but still used the reference above as C5, it would throw the data off by a row.

Share this post


Link to post
Share on other sites
Originally posted by m_c_meyer:

Anyway to import more than just two other site's projections(other than entering them over a previous year's stats for example)?

 

Also, why is the inputs tab protected? I think I could probably fix the above problem here, with a little tinkering of the fomulas.

 

Thanks!

 

BTW, everyone laughed at me for bringing my laptop to the draft last year. But after my draft, using the compiler and draft buddy, they weren't laughing anymore...especially after I went to the playoffs in my rookie season in a supposedly tough league of FF veterans! :D ;) :eek:

 

Hey m_c. I not sure you're going to be able to do all the necessary changes to add another source in the Compiler. It won't be just formulas on the inputs tab, but stuff in the macros too.

 

An idea however: put the projections (as many as you want) in a separate spreadsheet and average them together how you would like to, and then import those averaged projections into the Compiler in either Site B or Site C.

 

In fact, you could just create copies of the Compiler, input projections in Site A, B, C in the new copy, average as you'd like, and then import to your main working copy of the Compiler.

 

Nice work last year. We'll see if anyone else in your league shows up with a laptop this year! :)

Share this post


Link to post
Share on other sites
Originally posted by Mike MacGregor:

 

Hey m_c. I not sure you're going to be able to do all the necessary changes to add another source in the Compiler. It won't be just formulas on the inputs tab, but stuff in the macros too.

 

An idea however: put the projections (as many as you want) in a separate spreadsheet and average them together how you would like to, and then import those averaged projections into the Compiler in either Site B or Site C.

 

In fact, you could just create copies of the Compiler, input projections in Site A, B, C in the new copy, average as you'd like, and then import to your main working copy of the Compiler.

 

Nice work last year. We'll see if anyone else in your league shows up with a laptop this year! ;)

 

Thanks. I was actually thinking the same thing riding home from work the other day. Guess all I would have to do is change the Stat Allocation as I see fit(or depending ont the # of sources I'm averaging by) to make it work.

Share this post


Link to post
Share on other sites

VLOOKUP HELP!

 

Here is the formula that I am using, VLOOKUP(C5,[football1.xls]Sheet1!$C$3:$P$66,7).

 

The formula is in Cell CJ5 of The Compiler, and the data that I am referring to is in a XCEL Spreadsheet, sorted by First Name of QB. the names are in column C and the data is in column H. In the compiler, Cell C5 is Donovan McNabb. In my spreadsheet, Donovan McNabb is in cell C19, the formula should pull data from cell I19, but gets the data from cell I18.

 

HELP!!!!!!!!!!!!!!!!!!

Share this post


Link to post
Share on other sites
Just a word of caution - when importing other data and editing the names watch out for the rookie designations in the cheat sheet. That is the "r" with a circle around it, it is part of the name and hence has to exactly match when using the vlookup command

 

To make the r with a circle around it symbol at the end of the name hold down your ALT key while typing 0174 Then release the ALT key and you'll have that symbol so your names will match. ;)

Share this post


Link to post
Share on other sites
Originally posted by chtrich:

 

To make the r with a circle around it symbol at the end of the name hold down your ALT key while typing 0174 Then release the ALT key and you'll have that symbol so your names will match. ;)

 

Or what I do is click on a cell that has the circle-R symbol, and in the formula bar highlight just the symbol with the mouse. Hit CTRL-C to copy it, and then where you want to place it (say in Replace box of Edit > Replace function), then hit CTRL-V to paste it.

Share this post


Link to post
Share on other sites
Originally posted by usnrskc:

VLOOKUP HELP!

 

Here is the formula that I am using, VLOOKUP(C5,[football1.xls]Sheet1!$C$3:$P$66,7).

 

The formula is in Cell CJ5 of The Compiler, and the data that I am referring to is in a XCEL Spreadsheet, sorted by First Name of QB. the names are in column C and the data is in column H. In the compiler, Cell C5 is Donovan McNabb. In my spreadsheet, Donovan McNabb is in cell C19, the formula should pull data from cell I19, but gets the data from cell I18.

 

HELP!!!!!!!!!!!!!!!!!!

 

I think I need to see this to give an educated answer. Can you send me your projection spreadsheet (football1.xls), and just copy the QB tab into it so you don't need to send me the whole Compiler file. Click on QB tab in Compiler, choose Edit > More or Copy Sheet, click "Create a Copy" and choose to copy to the football1.xls spreadsheet.

Share this post


Link to post
Share on other sites

Just a note for everyone, usnrskc was having two problems with his import, which could easily happen to anyone.

 

1. If you are importing projections from Footballguys, then you must read this thread.

 

2. Make sure to include the last part of the VLOOKUP formula ,FALSE) or ,0) (same thing) so Excel is looking for an exact match of player names. If it doesn't find an exact match, it will throw an error, but that is better than putting stats with the wrong players.

Share this post


Link to post
Share on other sites
Guest Moose Knuckle Ball Four

Post of the year!

Share this post


Link to post
Share on other sites

This is a great post, however I'm having problems with copying down the vlookup. For some silly reason Excel is incrementing all of the values from the 'Site B'! sheet.

Example:

CJ:

1: =Vlookup($C5,'Site B'!A2:K38,2,False)

2: =Vlookup($C6,'Site B'!A 3 :K 39 ,2,False)

 

How can I prevent this? I have tried manually entering the second row also and then filling out the rest of the column from those two, but it still is incrementing the Site B sheet values along with the $C5 values, and this results in #N/A even though the name is exactly the same (since the name may be in 'Site B'!A2 position, but due to the incrementing it is only doing lookup for 'Site B'!A13 or something.

 

Thanks (hope you followed this).

Share this post


Link to post
Share on other sites

Having trouble having compiler idetify the projections I'm trying to import. How do you get it to see the sheet you have your stats on, is there somwhere you type it in? Can you just give me a quick run down of the steps needed to get it to idenmtify the imported stats. I think I understand the VLOOKUP formulas but can't get compiler to put new stats in as site B or C. Any information would be appreciated. Thanks.

Share this post


Link to post
Share on other sites

Hey plod. I follow you. This catches me all the time, but all you need to do is throw some dollar signs around the references to keep them the same when you copy the formulas. Go from this:

 

=Vlookup($C5,'Site B'!A2:K38,2,False)

 

to this:

 

=Vlookup($C5,'Site B'!$A$2:$K$38,2,False)

 

Note, the reason we do not put the dollar sign beside the 5 in the section $C5 at the beginning, is because you do want the row number to change for the player name you are looking up as you copy cells down the sheets.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×