Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome

How is the data listed in the spreadsheet now? Can you give an example?

"Andy M" wrote:

Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome

Are you looking for the highest total score for all competitions, the highest
score in any competition or the highest score for an individual competition
seen by the team?

do you want an indication there is a tie if one is present?

"Andy M" wrote:

Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome

Data arranged something like

Game 1 Game 2 Game 3
Team 1 Player 1 123 134 101
Team 1 Player 2 132 150 90
Team 2 Player 3 102 150 105
Team 2 Player 4 95 149 107

For each game I want to be able to locate the highest score recorded by
whichever team member, so in another table i'm looking for

Game 1 Game 2 Game 3
Team 1 132 150 101
Team 2 102 150 107

where the game scores in the "team only" table are the higher of the two
results recorded by that teams players. You can assume a tie between players
in the same team is not possible.


"Barb R." wrote:

How is the data listed in the spreadsheet now? Can you give an example?

"Andy M" wrote:

Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome

Please refer to response to barb R - hopefully this makes it (a little)
clearer ?!

"bj" wrote:

Are you looking for the highest total score for all competitions, the highest
score in any competition or the highest score for an individual competition
seen by the team?

do you want an indication there is a tie if one is present?

"Andy M" wrote:

Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome

I think I've got this - if I create a hidden column (to the left) of the
team and player names which contains a concatenation of Team and Player
I can then create tables elsewhere in the work book something like this.

A B C D
1 Team 1 Player 1 Player 2 =max(vlookup(A1&B1,<lookup
range
name,<column
offset,true),vlookup(A1&C1,<lookup range name,<column offset,true))

which I think does the job!!

Thanks to those who gave it some thought!


"Andy M" wrote:

Table containing team names and members of teams of two and their scores in a
number of competitions. Require to return the highest scorer for each team.
Can use vlookup to return one of the records for a team though not
necessarily the team member with highest score. Can't sort the array by
descending order of points scored because the highest scorere for any given
team will not be the same in every competition. Could probably think of a
way of doing this by writing some VBA but if anyone can think of some way of
avoiding that.....all ideas welcome