Ranking Issue

I have a simple table, two columns A and B. In B1:B40 I have a list of
names, in A1:A40 are a list of numbers sorted from highest to lowest.
Ultimately what I'm trying to do is to get a ranking of the top 5
scores but, when there is a tie (duplicates in A) to take the names in
B next to the duplicate scores and Concatenate them in C. I've tried
using Rank, Vlookup, using Excel's FILTER and none of them work for
what I'm trying to do. My thought is to try to find a function that
will look through the numbers in A and when it finds duplicates
concatenate the matching names in a cell in C but as Vlookup only
returns one result it isn't working. Anyone have any ideas?

One of the problems with these rankings is that the 'Top 5' scores could actually be down through C6 or hypothetically, even C10 or lower (see example below):


First line ("23 John Doe" is Row 1)
(example 1)


A B
23 John Doe
22 Jane Doe
22 Bob Jones
21 John Smith
20 Lisa Johnson
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Williams
18 Luke Jenkins
17 April Jones


The results I am looking for would be:
(example 2)


A B C D
23 John Doe John Doe 23
22 Jane Doe Jane Doe, Bob Jones 22
22 Bob Jones John Smith 21
21 John Smith Lisa Johnson, Sue Jones 20
20 Lisa Johnson Cathy Stanford, Jack Ford, Bill Wall 19
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Wall
18 Luke Jenkins
17 April Jones


So in example 2, it is finding the top scores, even down through the
duplicates, and concatenating the names in C (I forgot to mention in
the first post I also need to put the number for column A next to the
result in C.

I don't think you can do this easily with "native" excel formulas. One reason for that is that there is no inbuilt function in excel which concatenates an array.

Here's an approach you could try if you don't mind using functions from Morefunc add-in [ COUNTDIFF and MCONCAT ]

Using column D as a "helper" column put a 1 in D1 then in D2 copied down to D40 this formula

=COUNTDIFF(A$1:A2)

then in C1 copied down to C5

=MCONCAT(IF(D$1:D$40=ROW()-ROW(C$1)+1,B$1:B$40&", ",""))&INDEX(A$1:A$40,MATCH(ROW()-ROW(C$1)+1,D$1:D$40,0))

confirmed with CTRL+SHIFT+ENTER

for Morefunc see here

http://xcell05.free.fr/