Profiles and Projections Cell Agreement

[zEli173]

For future updates, can you please arrange so that the cells for the profiles and projections match up with one another?

I like to copy and paste the profiles into the projections; I find it necessary to have this context in order to make sense of the profiles. But there are a large number of players who have a profile without any projection or a projection without any profile. This makes the merging of the two info sets an arduous process of deleting cells until everything matches up. I've been working at my merged sheet for the last half hour and I'm still on names starting with "H". Going forward, please add the necessary filler cells for players skipped over in the profiles and projections so copying and pasting is an easy job.

Thanks.

[Todd Zola]

Sorry, we have a 2-man staff and will not be able to provide everything in a perfect format.

That said, there is an excel function that will enable you to do what you wish in less than 1 minute.

Please shoot me an e-mail describing what you want linked and I can either do it or explain how to do it, or better yet, both.

[Kelly_Leak]

Sorry, we have a 2-man staff and will not be able to provide everything in a perfect format.

That said, there is an excel function that will enable you to do what you wish in less than 1 minute.

Please shoot me an e-mail describing what you want linked and I can either do it or explain how to do it, or better yet, both.

behold the power of the vlookup!!!

[50 Desert Eagles]

Due to inflation, my .02 cents isn't worth NEAR as much now, but here it is....For a 2 person staff, You guys kick @$$....its like you answer every question within 24 hrs regardless and your professionals about it....Now I will have to say I see more posts from Todd then Gary, but its not my place to say who does the most work Anyway keep up the good work moderators!!!!!

[Todd Zola]

I'm a board rat, always have been which is a major reason why I brought the site back. Trust me, Gary is plenty busy

[Guest]

Desert - I think there are each sort of areas of the board we both like to hit, so maybe thats where your impression comes from, but my wife certainly thinks I'm on this message board a lot.

Also, we have that little issue of producing content, but I would certainly agree that Todd kicks serious ass on this board and I am very, very lucky to work with him.

[msugray]

I will show ONE method of doing this. I am not going to go into the many, many ways of using VLOOKUP. Books are written about such things and Excel will do much more than most people will ever use. I consider myself a novice in Excel, but can get around a little.

This example will will use the unmodified "hit_02_15_09" projections download and the "Hitting_Profiles_02_01_09" download

Simple VLOOKUP format: vlookup(what are you looking for, what table to you want to find it in, what value from table do you want once you find it, does match have to be exact or is close enough ok)
It will look something like =vlookup(A2,$B$2:$F$100,3,false). This is basic format. It will search for whatever value is in A2, it will look for it in any of the cells from B2 to F100, the number 3 tells it which column in the table I want if it does find it (in this case column d), the false tells it that I want exact match.

1) Open "hit_02_15_09"
2) Open "Hitting_Profiles_02_01_09"
For this demo I will add the profile of each player to the "Hitters_02_15" sheet in the projections. It will have Reggie Abercrombie in cell A2 followed by all the stat projections for all players.
4) Click in cell AA2 (the first available cell at the end of the Reggie Abercrombie row)
5) Type: =vlookup(
6) Click on Reggie Abercrombie; A2 should show up in your formula
7) Type: ,
Now you're ready to set the table you want to search to return the profile
8) Click on workbook "Hitting_Profiles_02_01_09"
9) Click on Tab "Hitting profiles"
The players are listed in Column A and the profiles are listed in Column B.
This is the table you want to search and the table from which you want to bring the profiles in from.
10) Left click on Reggie Abercrombie (cell A8), hold down left mouse button, drag down through Ben Zobrist in row 540, drag across to select column B which contains profiles, release mouse button to select
11) Type: ,
12) Type: 2
If it does find your player in the table, you want the profiles which are in column 2
13) Type: ,
14) Type: false)
15) Enter

Reggie Abercrombie's profile should now be in cell AA2 at the end of his projection.

16) Drag this formula in Column AA down through all of the other hitters to Copy.
I just click in cell AA2, grab the lower right-hand corner of the cell with the right mouse click button, drag down through all rows, release mouse button and select "Copy cells"

Profiles should be there.

It will show a #N/A if it can't find a profile. Yes this can be gotten rid of, but I'm not going into that. You can get it to just say "No profile" using an IF(ISNA) function.
Other method will be needed if the names you're searching for are in ANY way different in the table. Spelling, format, or even spaces will cause problems with this simple method.

I don't know if this will be helpful to anyone or not.

Probably left some more confused than before.

Once you grasp the simple concept of VLOOKUP, you can see where you could expand the usages.

[Todd Zola]

I'll add that after doing this, you can get rid of the formulas and make it look like seamless text by highlighting the newly created profiles, copy - right click to paste special then choose values.

Also...

There are 2 pitchers named Ramon Ramirez. I may have done this, I forget. If you put a period "." after the name of one on both the projection and profile sheet, it will pull in the correct profile.

I MAY have already done this with the CIN version and made the text white so it will not be seen.

If there is an #n/a, we will catch up with those profiles this weekend.

[50 Desert Eagles]

Hey Gary, no harm meant, I just have dealt with Todd a few times and never with you, thats all I was going on....well I just now noticed his 444 posts to your 73, but who's counting...I actually didnt know for about a month that you were a moderator, I do though stick to a few forums that I feel I can either help in or have a question in....I know the feeling on the wife though...I have been typing this reply for the last 3 hours, between potty training one, burping another and putting one in bed, once at home, no time to spend on a forum much...anyway, didnt want you to take it the wrong way...After all, if your working with Todd, its obvious you have some smarts and knowledge....later guys....someone just yelled poopy coming out NOOOOOOOOW......

[cwk1963]

Hey Gary, no harm meant, I just have dealt with Todd a few times and never with you, thats all I was going on....well I just now noticed his 444 posts to your 73, but who's counting...I actually didnt know for about a month that you were a moderator, I do though stick to a few forums that I feel I can either help in or have a question in....I know the feeling on the wife though...I have been typing this reply for the last 3 hours, between potty training one, burping another and putting one in bed, once at home, no time to spend on a forum much...anyway, didnt want you to take it the wrong way...After all, if your working with Todd, its obvious you have some smarts and knowledge....later guys....someone just yelled poopy coming out NOOOOOOOOW......

A little too much information .