I'm in a fantasy football league I'm in that's extremely numbers-based. Lots of data analysis to do (fictional players so it's year round with several seasons each year).

The league provides a massive CSV file that contains all data for all players in the entire league. This is a large-ish file with 2700 players and all their attribute data (name, position, height, weight, strength, intelligence, speed, etc.) There are literally dozens of attributes and all of them appear in the master CSV even though each position only uses a subset of them (a Center has a "Snapping" attribute (his ability to snap the ball to the quarterback) while a Running Back would have no such value there as he never does that.)

The main issue is to then have sheets for each position to make viewing and analysis simpler. Plus, this data changes often so the goal is to just be able to re-populate the master sheet as needed and have everything else auto-populate. Nothing will happen on the master sheet. It's just a placeholder of all the data.

I'd then have a sheet for each unique position type. One for a QB (quarterback) and that would need to look at the master sheet and find all entries where Position = QB and copy those rows to the QB sheet.

However, remember that each position only uses a subset of the overall attributes so I wouldn't want to grab the full row. I'd only want to get specific parts of the row relevant to each position ignoring anything unneeded.

Then, lastly, on those position-based sheets that's where all my calculations will be. Players are rated in each attribute on a scale of 0-99 and my approach gives players a "bonus" for being above a certain number for each attribute but a heavy "penalty" for being below that number.

As an example, take an offensive lineman. Strength is a key attribute for them. To be a good lineman you must have a strength of at least 80. My calculation for Strength for linemen might be 1 and 10. In other words, for every point above 80 you get a point but for every point below 80 you lose 10 points.

The point of the above is to make it clear that each positional sheet will have lots of formulas and such on it so there's more to consider than just dumping data from one sheet to another. The target sheet also needs to be able to preserve the formulas that are there.

How can I automate this?

Thanks....

With great difficulty would be my initial answer...

Sounds like something more suited to Access perhaps...

However, Anything is possible given enough time to work it out, so perhaps if you post a copy of your workbook so far that might help.

The file seems to be too big to upload. It'd be pretty hard to get it down to 90k from 21MB.

one of the options is to use a realy looooooong if formula.

=if(a1="qb",a1,if(a1="second postion","second postion",if(a1="3rd position", 3rd position, if(...

another option is to do this semi-manually:
1)highlight the 1st row (where all the row titles are"
2)press the "filter" button. You can find it on the tool bar under the"sort & filter" button.
3) now you can press on the little arrow in the 1st line in the row of positions and mark only the position you are intrested in (let's say "qb")
4)highlight the whole spreadsheet and "copy"
5) go to a different spreadsheet (named "QB") and just paste
6)repeat for every position and every time the "Master Spreadsheet" is updated

Cheer up it's not as much work as it sounds

On Fri, 6 Apr 2012 07:03:22 +0000, Agrajag
wrote:


I'm in a fantasy football league I'm in that's extremely numbers-based.
Lots of data analysis to do (fictional players so it's year round with
several seasons each year).

The league provides a massive CSV file that contains all data for all
players in the entire league. This is a large-ish file with 2700 players
and all their attribute data (name, position, height, weight, strength,
intelligence, speed, etc.) There are literally dozens of attributes and
all of them appear in the master CSV even though each position only uses
a subset of them (a Center has a "Snapping" attribute (his ability to
snap the ball to the quarterback) while a Running Back would have no
such value there as he never does that.)

The main issue is to then have sheets for each position to make viewing
and analysis simpler. Plus, this data changes often so the goal is to
just be able to re-populate the master sheet as needed and have
everything else auto-populate. Nothing will happen on the master sheet.
It's just a placeholder of all the data.

I'd then have a sheet for each unique position type. One for a QB
(quarterback) and that would need to look at the master sheet and find
all entries where Position = QB and copy those rows to the QB sheet.

However, remember that each position only uses a subset of the overall
attributes so I wouldn't want to grab the full row. I'd only want to get
specific parts of the row relevant to each position ignoring anything
unneeded.

Then, lastly, on those position-based sheets that's where all my
calculations will be. Players are rated in each attribute on a scale of
0-99 and my approach gives players a "bonus" for being above a certain
number for each attribute but a heavy "penalty" for being below that
number.

As an example, take an offensive lineman. Strength is a key attribute
for them. To be a good lineman you must have a strength of at least 80.
My calculation for Strength for linemen might be 1 and 10. In other
words, for every point above 80 you get a point but for every point
below 80 you lose 10 points.

The point of the above is to make it clear that each positional sheet
will have lots of formulas and such on it so there's more to consider
than just dumping data from one sheet to another. The target sheet also
needs to be able to preserve the formulas that are there.

How can I automate this?

Thanks....


This is not exactly what you are after, but does carry many of the
elements, and has the benefit of being about football, AND has a good set
of team helmets to use.

http://office.microsoft.com/en-us/te...101939767.aspx

The 2009 version is better because I think it has the data filled
in,but if you fill in the game stats, the green and red zones all work
right.

Took me a few iterations to get it somewhat right. Haven't worked on
it since they messed with the template site.