Marcus Coates
New member
- Joined
- Sep 11, 2012
- Messages
- 12
- Reaction score
- 0
- Points
- 0
Hi I am after some help with a formula for a spreadsheet.
I have to compile results from three cross country races which will determine who are the overall winners.
Race1
Column A would have the runners names column B their position. These columns could be interchangeable if that would help but they would be sorted from first place downwards
Race 2
Results would be repeated be in columns C (name)and D (position)
Race 3
Columns E and F
Keeping it simple let's say john smith runs in all three races in race 1 he is 1st race 2 he is 10th and race 3 he is 5th.
What I want to do in a new column is list every person that has been in the races, but I do not want duplicates. Then in the next several columns display their positions
So for example in column G john smith's name appears in columns H I J it will display his positions he finished.
Some people might be only in one race others might be in all three, but to determine the winners we use the two best positions out of the three and add them together so for john smith his score would be 6.
I have seen a vlookup for matching two columns and then pulling the value from the cell next to it.
I'm just not sure what criteria I need to ask excel? Any help would be greatly appreciated.
I have to compile results from three cross country races which will determine who are the overall winners.
Race1
Column A would have the runners names column B their position. These columns could be interchangeable if that would help but they would be sorted from first place downwards
Race 2
Results would be repeated be in columns C (name)and D (position)
Race 3
Columns E and F
Keeping it simple let's say john smith runs in all three races in race 1 he is 1st race 2 he is 10th and race 3 he is 5th.
What I want to do in a new column is list every person that has been in the races, but I do not want duplicates. Then in the next several columns display their positions
So for example in column G john smith's name appears in columns H I J it will display his positions he finished.
Some people might be only in one race others might be in all three, but to determine the winners we use the two best positions out of the three and add them together so for john smith his score would be 6.
I have seen a vlookup for matching two columns and then pulling the value from the cell next to it.
I'm just not sure what criteria I need to ask excel? Any help would be greatly appreciated.