selecting from matrix

[pronkrug]

Capture.PNGConsider the following matrix (as an example):

1st player/ team 2nd player/team 3rd player/ team
game1 pl A tm1 pl B tm2 pl C tm 1
game2 pl D tm2 pl A tm1 pl E tm 3
game3 pl E tm 3 pl A tm1 pl C tm 1
game4 pl B tm2 pl F tm3 pl A tm1

A first place earns 10 points, second place 7 and third 3 points. For both team and player.

For the players I can make a total list, because every player is unique per game. So a check per column using vlookup did the trick. Maybe there is a better/easier/faster way, but it worked. However the team isn't unique. Any suggestions on how to solve this?

player would be:
G1 G2 G3 G4 Tot
pl A 10 7 7 3 = 27
pl B 7 0 0 10 = 17
pl C 3 0 3 0 = 6 etc

Team should be:
G1 G2 G3 G4 Tot
tm1 13 7 10 3 = 33
tm2 7 10 0 10 = 27
tm3 etc

[pronkrug]

Capture.PNG

trying to attach the picture

[PlusX.me]

Maybe you can sum up the players:

Team 1=plA points + plC points
Team 2=plD points + plB points ?

[pronkrug]

That would work for 1 year, but the goal is to have this for several years. And a player is allowed to change team. In very rare cases this might even happen during 1 season.

[PlusX.me]

May not solve your problem but in general:

You want to use this file for several years, but the tables are not organised well enough; as the games go on, the sheet will grow vertical (upper table) and horizontal (lower tables). The opposite orientation also has effect on formulizing the totals.

I would change that and then the rest is not that difficult.

[PlusX.me]

May not solve your problem but in general:

You want to use this file for several years, but the tables are not organised well enough; as the games go on, the sheet will grow vertical (upper table) and horizontal (lower tables). The opposite orientation also has effect on formulizing the totals.

I would change that and then the rest is not that difficult.

[pronkrug]

Hi PlusX.me,
I realize that, but this is just a simple representation of the actual file. The actual file consists of several sheets and much more information, such as location, country of origin etc. But I had hoped that simplifying the question would make it easier to get an answer :-)
Sorry, for the misunderstanding.

[pronkrug]

Hi PlusX.me,
I realize that, but this is just a simple representation of the actual file. The actual file consists of several sheets and much more information, such as location, country of origin etc. But I had hoped that simplifying the question would make it easier to get an answer :-)
Sorry, for the misunderstanding.

[PlusX.me]

Maybe you should simplify the file instead of the question.
If it is organised like the example, it seems to me like most of the calculations have to be done manually.