Excel help (sorry lol) - Fantasy Baseball Cafe 2015 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Excel help (sorry lol)

Moderator: Baseball Moderators

Postby gostanford07 » Thu Mar 09, 2006 12:41 am

Thanks for all the support guys. Im making progress but still need a little help. Is there an easy way to make the player names uniform in all of the sources. For instance, one source lists players by Last, First, Team whearas another is simply Last, First. Is there a way for me to make a standardized format for all of my sources without having to manually edit each one. Also, is there a way to automatically make the lists have the same order... that is, how do I quickly edit the rankings of position without losing the values I have for their stats. Thanks again
gostanford07
Minor League Mentor
Minor League Mentor

User avatar
Eagle Eye
Posts: 546
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane

Postby TopChuckie » Thu Mar 09, 2006 12:54 pm

gostanford07 wrote:Thanks for all the support guys. Im making progress but still need a little help. Is there an easy way to make the player names uniform in all of the sources. For instance, one source lists players by Last, First, Team whearas another is simply Last, First. Is there a way for me to make a standardized format for all of my sources without having to manually edit each one. Also, is there a way to automatically make the lists have the same order... that is, how do I quickly edit the rankings of position without losing the values I have for their stats. Thanks again


I would have to see the info in all your columns to answer the last part, but I assume you have a column with positions in it and you can sort by that column first. When you sort you need to make sure you select all your columns of data for the sort range and then all the values in each column will remain associated with the column you are sorting.

Formatting all the names the same is a little trickier, but as I said in my post above two helpful tricks are "Text to Columns" and "Concatenate".

What you want to do is get all the players' first names in one column and all their last names in another column with the use of Text to Columns, you can split the names into columns at a space, i.e. Ken[space]Griffey, or at a comma, i.e. Griffey, Ken or Griffey,Ken.

Then you use concatenate to put the names back together in whatever format you prefer. I like "Griffey, Ken."

Once you THINK you have all the names formatted the same you will want to check to insure all the names match and you don't have a rogue "Jr" or middle initial in one of your sources which will throw off your match. There are probably several ways to expedite this check, but I do it via VLOOKUP also.
TopChuckie
Softball Supervisor
Softball Supervisor


Posts: 78
(Past Year: 7)
Joined: 3 Feb 2006
Home Cafe: Baseball

Postby gostanford07 » Thu Mar 09, 2006 10:32 pm

Ok, here's an example. Say one of my 1B lists looks like this:
PLAYER, TEAM
Albert Pujols, StL
David Ortiz, Bos
Mark Teixeira, Tex
Derrek Lee, ChC
Travis Hafner, Cle
Todd Helton, Col
Adam Dunn, Cin
Lance Berkman, Hou
Paul Konerko, ChW
Richie Sexson, Sea

Now my second source has the following top 10:
PLAYER, TEAM
Albert Pujols
Mark Teixeira
David Ortiz
Derrek Lee
Todd Helton
Travis Hafner
Richie Sexson
Lance Berkman
Paul Konerko
Carlos Delgado

In the other columns I have their respective projections. I have aligned each source so that the projections are all in the same order. All I need to do is now re arange the second list (keeping each player's stats intact) so that I can combine the two into an overall projections page with the averages. How do I do this... both rearrange the lists to be in the same order and combine them in a new list with the averages. Hope this helps and thanks again
gostanford07
Minor League Mentor
Minor League Mentor

User avatar
Eagle Eye
Posts: 546
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane

Postby TopChuckie » Fri Mar 10, 2006 10:37 am

gostanford07 wrote:Ok, here's an example. Say one of my 1B lists looks like this:
PLAYER, TEAM
Albert Pujols, StL
David Ortiz, Bos
Mark Teixeira, Tex
Derrek Lee, ChC
Travis Hafner, Cle
Todd Helton, Col
Adam Dunn, Cin
Lance Berkman, Hou
Paul Konerko, ChW
Richie Sexson, Sea

Now my second source has the following top 10:
PLAYER, TEAM
Albert Pujols
Mark Teixeira
David Ortiz
Derrek Lee
Todd Helton
Travis Hafner
Richie Sexson
Lance Berkman
Paul Konerko
Carlos Delgado

In the other columns I have their respective projections. I have aligned each source so that the projections are all in the same order. All I need to do is now re arange the second list (keeping each player's stats intact) so that I can combine the two into an overall projections page with the averages. How do I do this... both rearrange the lists to be in the same order and combine them in a new list with the averages. Hope this helps and thanks again


One question I have is are you sure both lists have all the same players and only the same players?

In the situation above the first thing I'd do is format all the names LastName, FirstName. To do this I would select the column of cells with the names in it and do Text to Columns-Delimited-Space & Comma. For your first list this would give you a column with Albert, David, Mark, etc.; another column with Pujols, Ortiz, Teixeira, etc.; and a third column with StL, Bos, Tex, Etc. Then I would insert the Concatenate function and chose a cell from the last name column, i.e. Pujols, then input ", " in the second field, and then chose a cell from the first name column, i.e. Albert. This will result in Pujols, Albert. Copy that formula down the column for all the players and you will have all the names formatted as Lastname, Firstname. Do a Copy-Paste Values for all those cells to get rid of the formulas and then you can delete the first name colum and last name column and just use the formatted name column. Once you use these features and see the windows that come up as you use them it should make more sense.

You would format the names from the second list in the same way except when you Text to Columns you wouldn't need to use the comma, just space.

Once you've done that, if you do have all the same players on both lists and only the same players on both lists you could sort by name alphabetically and all the players should be in the same order. Remember, when you sort you need to select the entire field of data, all columns, so that the stats remain associated with the correct player name. If you leave your players as Firstname Lastname you could still sort alphabetically, but I'm a perfectionist and that's sloppy to me.

Since most sources include different players, this is where I use VLOOKUP to associate the stats (or in my case dollar values) from each source with that player's name. I'm not familiar with Pivot Tables so I can't say if that is a better method, though Mattias obviously thinks it is, so I will research that for future reference. In my case, after I use VLOOKUP to associate approximately (20) dollar values from different sources, I use the AVERAGE and MEDIAN functions.
TopChuckie
Softball Supervisor
Softball Supervisor


Posts: 78
(Past Year: 7)
Joined: 3 Feb 2006
Home Cafe: Baseball

Previous

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 8 guests

Forums Articles & Tips Sleepers Rankings Leagues


  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact