Originally Posted by Agrajag

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....