How to return duplicate max value

[Yweiss0]

Hi, I need your help with returning duplicate max values.
I have in column A numbers of winning in a game
And in column B the names of each player
In cell C2 i want to return the name of the guy who has the max numbers of winning so i use this command:
=INDEX($A$1:$A$16,MATCH(MAX($B$1:$B$16),$B$1:$B$16,0))
And its works.
The problem is when i have two people with same amoint of winning (lets say 5 times and thia ia the max value) the formula will return in C1 only the firat name..
How can i make it to write all the names that have the MAX winnig value
So in C1 i will have all the names that has the max value of winnings and i want it to be in the same cell so its should be: name1, name2, name3
Thank you!

 

[shg]

I use one of those UDF oddities all the time, to indent work breakdown structures. Never found much use for the others, and your example is the first I've seen.

 

[Peter_SSs]

I'm actually working outside my usual comfort zone here but thought I would give it a try.
I might have gone a bit overboard though? Perhaps I only need this?

<del>With Application.Caller.Parent.Cells(Application.Caller.Row, Application.Caller.Column)</del>
With Application.Caller

 

[Yweiss0]

Thanks So much! it's working perfect! and I've made one for the min value.

sorry for bugging but can you please try to explain it a little bit so i'll understand better what it does..

specially those lines -
Application.Volatile
c1 = "'" & rng.Parent.Name & "'!" & rng.Columns(1).Address(0, 0)
c2 = "'" & rng.Parent.Name & "'!" & rng.Columns(2).Address(0, 0)
Mx= Application.Max(rng.Columns(2))

Winners = Replace(Join(Filter(Application.Transpose(Evaluate("""|""&" & c2 & "&""|""&" & c1)), "|" & Mx & "|", True), ", "), "|" & Mx & "|", "")

 

[Peter_SSs]

Thanks So much! it's working perfect! and I've made one for the min value.

sorry for bugging but can you please try to explain it a little bit so i'll understand better what it does..

Application.Volatile
Forces the udf to be recalculated whenever calculation occurs in any cells on the worksheet

c1 = "'" & rng.Parent.Name & "'!" & rng.Columns(1).Address(0, 0)
Creates a string that references (including the sheet name) the range containing the names of the players. For example it would be something like 'Sheet1'!A2:A8
Note that shg has suggested a simpler way for that line of code - see post 29


c2 = "'" & rng.Parent.Name & "'!" & rng.Columns(2).Address(0, 0)
Same as above but it is for the range holding the scores


Mx= Application.Max(rng.Columns(2))
Calculates the maximum score so we know which names to extract. It works something like =MAX(B2:B8) would on the sheet with the data.


Winners = Replace(Join(Filter(Application.Transpose(Evaluate("""|""&" & c2 & "&""|""&" & c1)), "|" & Mx & "|", True), ", "), "|" & Mx & "|", "")
This does several steps
Creates an array where each element of the array consists of the players score (surrounded by | characters) followed by the players name. A small example would be
|5|Tom
|3|Ben
|5|Ann

Then the line filters that array to only include items that contain the maximum score (surrounded by | characters). So in this tiny example where 5 is the maximum score the array would be cut down to
|5|Tom
|5|Ann

Next, these array items are joined into a single string, separated by ", " so we get
|5|Tom, |5|Ann

Finally we replace the maximum score (surrounded by | characters) with nothing, which leaves
Tom, Ann

 

[Yweiss0]

Thanks a million!!

 

[Peter_SSs]

Thanks a million!!

You're welcome.