Excel help - no idea how to proceed with this

[Ragnarak]

I have an excel sheet that works out peoples scores for a game and sorts them into a league table.

What i need now is to take this league table and create an overall league table for each round played. This means that the overall table needs to keep a running total of each persons score and when a new round is played, to take the information from the league table for that round and to add that persons points to the overal league table.

Some problems:

Each round might have different people in it. Someone might be in every round or they might just play occassionally.

One round might have 100 people play it, another only 50 people. Some of the people will be the same but some could be different.

I'm not sure how i should go about doing this....Anyone help at all?

[Stew]

*Paging 1-X to thread 179235*

[1-X]

I'd like to help but...

I did it manually.

I have a separate spreadsheet for each of the prediction leagues, and another called allleagues.
Allleagues has a row for every player who has ever entered, and columns for X/G/Pts for all leagues (and a few misc stats).
At the end of each league, I type the numbers in the boxes. (the results from the latest league into 'allleagues')

I know it can be done the way you want (with multiple sheets and 3D references), it just seemed like too much hard work and I never got round to it.

[Stew]

I'd give each entrant a code, have the players down the side, diff leagues along the top and use vlookup

[JetLinus]

Hm, to solve this in VBA / macro-style:

=> Compile a list of all players:

1. Cycle through all rounds
2. For each round, cycle through all players
3. If a player doesn't exist in your list (array, collection, whatever) yet, add him

Now, you work through that list:

1. Cycle through every player in that list
2. For each player, cycle through all rounds
3. Now cycle through all the players from that current round.
4. If this player name matches with the orignal one from your list, do your statistics stuff for it (i.e. adding up scores, whatever)

Eventually you've got your original list with data "attached" to it, just as you wanted...

Only problem with this I can see is performance: For every player, this algorithm needs to run through every "account per round" played, which could take a bit. Optimization however would be possible, if really needed....