So I have this project that I Have been working on for some time. It is very large and very complex but I will simplify an example to make it easier to understand. I currently have the program running and working my problem is that it is HUGE and I feel like there must be a simpler way of doing it but I can't quite put my finger on it so I am coming here in search of inspiration. Any ideas will be greatly appreciated but I am trying to make this spreadsheet out of formulas and not Macros. So here it goes:

I have attached a spreadsheet with a simplified example of what I want to do. Basically I have raw data from a competition between myself and my friends and I am in charge of analyzing the statistics. Each "round", four "players" compete in a competition. Two players are on the red team and two on the blue team. Every round a different game is played (in my example Monopoly, Life, and Clue) and one team wins. Also each round every player is a character. I want to be able to take this data and calculate a very large amount of statistics (keep in mind my actual problem has a lot more "players", "characters" and "games", though it is always 4 people playing on teams of two and one team always win, no ties).

I want to be able to calculate things like the winning percentage of each player, character, and team while also being able to calculate what a team's winning percentage at each game is, their favorite opponent to play against, their favorite team to play against, etc. The current way that I do that is probably way more complicated than need to be: I assign a prime number to every single "player", "character", "and "game" and then organize the data into columns of winners and losers. I multiply the different combinations together that I want stats for to get new numbers (for instance if I wanted a stat for how many times a player played with each character I would multiply the number for each player by the character number and create a new column. Then I would count how many times that number showed up in the winners column and that would be their total wins as that character). In order for me to figure out what number the combination of James and Professor Plum are I have to make large matrices showing every combination.

That is where the problem occurs. I have the HUGE matrices that show the combination of each player, character, and game. I was thinking maybe there is a way that I can combine the whole "data" into one text string (as shown in my example in column Q) and somehow count how many times "James-Professor Plum" shows up or something along those lines. I did at one point try to multiply all the number values I assigned from the raw data together and come up with a huge number. Because all the numbers are prime you would only be able to divide evenly by each of the prime numbers but the number got into the trillions and thus did not work for several functions I used.

I am really looking for any ideas or some new way to look at this. I have been working on it for quite some time and what I have done works fantastic. But it it HUGE and runs somewhat slow coupled with a Macros program my friend wrote. I do need my solution to stay in the form of a formula however. If you need any more information or have any ideas please let me know. I appreciate the time spent reading this as well. Thanks

PS I left some spots under the data to show what kind of stats I am looking for (I only gave two but there are dozens that I will actually be calculating). Also the data is continually being added. This will be an ongoing thing and data will constantly be added to the spreadsheet.

Help.xlsx