Finding the "best" (top) 20 values and assigning a name for each

[valiantSeven]

Hi there,

I'm not exactly new to Excel (2007), but I've just been introduced to the world of macros. I had no clue they could even be written until a few hours ago! I'm assuming I'll need one to complete the task I'm after, maybe, maybe not! We'll see!

I have a workbook that has three sheets in it. In each sheet, column A contains a list of names on a roster. Columns B through M contain differing values that correspond to the person's name in that row. At the end, in column N, a calculation is performed on all of those values to get a total score for the person's name that's in that row.

What I'd like to do is create a separate sheet that searches the three sheets in question for the highest 20 total scores it finds and list them in a descending order on the fourth sheet. However, when it lists them, I'm looking for it to list the score AND the name that the score is associated with.

Sounds relatively complicated to me, but pretty important for what purpose the workbook holds! Thanks a lot in advance, the help is very much appreciated!

[BadlySpelledBuoy]

I don't think you NEED a macro for that. Sounds like it could be done with formulas alone.
Although to provide that solution we'll need to see a (desensitized if necessary) version of the workbook.
Any chance you could attach one with mocked up expected results?

BSB

[valiantSeven]

Surely, working on it now!

[valiantSeven]

Here it is! Thanks a lot in advance

[BadlySpelledBuoy]

There are several approaches to this but the best will depend on your actual workbook.

Would you always have 14 names per sheet or is that just for this example?

BSB

[valiantSeven]

Just for the example, one sheet goes up to about 140, the other to about 50, and the last to about 20. I just hacked them down a bit for the example sheet.

[valiantSeven]

It's getting quite late for me here, so I'm going to crash for a little while and then revisit this in the (later) morning! Thanks a lot for all you've done already

[valiantSeven]

Just checking back to see if anyone had found any sort of solution! I tried playing with some pivot tables and some consolidate functions but it didn't really put out the result I was looking for (that or I did it wrong!). If anyone needs any more information just let me know!

Thanks in advance

[karedog]

Maybe :

Please Login or Register  to view this content.

[jindon]

Incl. tie management.

Please Login or Register  to view this content.