gostanford07 wrote:Ok, here's an example. Say one of my 1B lists looks like this:
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:
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.