Excel 2007 : Find highest value from several lists

[trendkill]

Hi all.

I have six lists with score results from a series of tournaments that have been played - containing players in column A, and corresponding points (result) in column B - pretty straight forward there.

From these lists I need to compile one "final list" based on the points in these six seperate lists. The trick and where I am stuck is the following:

In the six seperate result lists, the same player can show up more than once. He/she could have participated in 1-6 of these tournaments - but regardless of how many times one player shows up (e.g. six times or only once), the combined result counting towards the "final list" for the players from these seperate lists should only consider the result from the best (top) three finish positions.

Example:

Player A has played all six tournaments and placed on various finish positions - only the best three results for this player should count for the "final list".

Player B has only played two of the total six tournaments - both these result should count for the "final list" as it is still top three finish positions.



Does anybody have a good idea on how to solve this? I thought it must be possible to do it through some VLOOKUP, COUNTIF or IF formulas here and there, but the exact combination of them I canīt really figure out.

Greatful for any help, and if an attached example is required or can help, please let me know.

Cheers

[NBVC]

...if an attached example is required or can help, please let me know.

Cheers

Welcome to the forum,

This would be a good idea... along with some expected results shown.

[trendkill]

Iīve attached an example.

Now I just included four tournaments as example but I have six in total. In this example the guy Iīve highlighted yellow is obviously the "winner" as he has placed 1st in all tournaments he has played - but still, itīs only top three positions for any participating player that should count towards the "final list".

Hope this helped a little bit further.

Thanks a lot.

[DonkeyOte]

I would probably think about re-arranging the data for the sake of further analysis... in the attached (XL2007 format) I create one sheet called PT_SOURCE which ties to your entry sheet, and then ran a PT off the PT_SOURCE sheet to give final results.

EDIT: Note file removed at 4:47PM UK Time owing to error in Col D, reposted on latter post.

[DonkeyOte]

I found another typo in Col D formula so I have reposted the attachment in a new post to avoid confusion... apologies for the mess!

[trendkill]

Thanks a lot for your help.