Is this the better way to go instead of MASS Index/Match formulas?

[Agrajag]

I have a spreadsheet I created (and a dozen or more tabs) to analyze sports data. The main sheet is a master sheet of the raw player data and it includes a unique player ID.

However, since all the positions are in one list and each position needs to be analyzed differently I end up having to populate each sheet with a slew of formulas that look like this:

=IF(ISNA(INDEX(Master!A$2:A$6500,MATCH("RB"&" "&$A2,Master!$CE$2:$CE$6500,0))),"",INDEX(Master!A$2:A$6500,MATCH("RB"&" "&$A2,Master!$CE$2:$CE$6500,0)))

With that the manual copying and pasting is just endless. However, with this the sheet is now slow and sucks memory. Changes take seconds per change.

The above is just to grab the NAME of the player and repeats down the rows for about 500 lines. Then I do that again for every attribute I need to track. One sheet could have literally thousands of these.

Pivot tables by default wouldn't easily let me fill SHEET 2 from the Master sheet. Essentially I want to say, "On this sheet, the RB sheet, look at all the data on the Master sheet and create an entry for every player that has a POS of RB." I also need to be able to apply apply formulas and sort the data once done.

Will this work and improve things for me?

Thanks.

 

[Agrajag]

The tabs for both positions contain 100% of all DE's. The results of the different formulas are what make that distinction between them. So, for example, in my case on DE on my team is clearly my best LDE while a totally different DE is my best RDE. They are both Defensive Ends but they're better at different things. As noted, the same thing happens in other positions and situations. Thus the position entry is just a generalization. As I said, I might be like others and decide to look at RB's (one position) 8 different ways to find out that maybe I want to move from one type of running back to an entirely different type due to how amazing one player is at that type over what I do now.

I'm amazed at how something so SEEMINGLY basic can morph into something so complex. heheh This has been one great learning experience.

 

[Agrajag]

Do look at the Settings Tab again and you'll see that there are several more entries than there are actual positions. Thus, there are also different formulas for each distinct position sub-type. I couldn't figure out how to resolve that initially so that's why I opted for lots of tabs--one for each position sub-type. I'd LOVE to retire that approach but I just don't know how.

 

[Laurent C]

If my understanding is correct, you are trying to determine would be the best players at a given sub-position, based on some metrics. My advice: calculate the LDE and RED metrics for every DE player, and then take the best players according to the results.

By the way, the guy at powerpivotpro.com has written some stuff about his "Great Football Project", that may interest you.

 

[Agrajag]

If my understanding is correct, you are trying to determine would be the best players at a given sub-position, based on some metrics. My advice: calculate the LDE and RED metrics for every DE player, and then take the best players according to the results.

I guess I explained things poorly. That is exactly what I do now. What I'm asking is how I do that all on the one sheet as you were recommending. Without that I'm back to the same slow sheet that takes a ton of memory due to having to Index/Match all the thousands of players time and again for dozens of attributes.

The LDE tab in the existing sheet contains all players with a position of DE. The RDE tab likewise contains the same list of players. The only difference between them is the attributes considered and the weightings that get applied.

So, on the Master sheet in the column for "Strength" I can only have one result.

 

[Agrajag]

BTW, I want to say thanks Laurent. Even without the biggest change getting dealt with yet, your other input has made for great changes in this effort already.

 

[Laurent C]

I guess I explained things poorly. That is exactly what I do now. What I'm asking is how I do that all on the one sheet as you were recommending. Without that I'm back to the same slow sheet that takes a ton of memory due to having to Index/Match all the thousands of players time and again for dozens of attributes.

The LDE tab in the existing sheet contains all players with a position of DE. The RDE tab likewise contains the same list of players. The only difference between them is the attributes considered and the weightings that get applied.

So, on the Master sheet in the column for "Strength" I can only have one result.

I meant you could add a new column for the LDE and RDE metrics. Both on the main sheet.

 

[powerpivotpro]

I just want to say that I salute the usage of Excel/PowerPivot for what is clearly a fantasy football project

 

[Agrajag]

Thanks Rob. If you actually saw the game and the analysis that goes on it'd make your head spin. This is not your grandfather's fantasy football game. It's barely even related. <grin> The spreadsheet I'm using to analyze the data has given all my accounting friends nightmares and they swore on a stack of bibles that they were all Excel experts of the highest order. Several tried just using straight pivot tables and each failed when they realized how specialized this was. There are literally dozens of attributes to consider for every player and, of course, each position requires an entirely different approach. When I started I thought this would be a quick little sheet. Boy was I wrong.