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!

 

[Yweiss0]

Thank you!
But i prefer the UDF. my only problem is that the value in the winnings can be change and i need the cells to auto update with the new value and i need also the comment to get update with the new value

 

[Peter_SSs]

But i prefer the UDF.

So do I (if no TEXTJOIN function), but you did ask if it could be avoided.

 

[Yweiss0]

And with the UDF function that fluff worte me..?
How to make it auto update the value in cell and the comment

 

[Peter_SSs]

Try this UDF instead

Function Winners(rng As Range) As String
  Dim Mx As Double
  Dim c1 As String, c2 As String
  
  c1 = rng.Columns(1).Address(0, 0)
  c2 = rng.Columns(2).Address(0, 0)
  Mx = Application.Max(Range(c2))
  Winners = Replace(Join(Filter(Application.Transpose(Evaluate("""|""&" & c2 & "&""|""&" & c1)), "|" & Mx & "|", True), ", "), "|" & Mx & "|", "")

  With Cells(Application.Caller.Row, Application.Caller.Column)
    If Not .Comment Is Nothing Then .Comment.Delete
    .AddComment.Text CStr(Mx)
  End With
End Function

Excel Workbook
	A	B	C
1	Name	Score	Winners
2	Tom Pearce	1	Peter Davy, Harry Hawke
3	Bill Brewer	2
4	Jan Stewer	3
5	Peter Gurney	4
6	Peter Davy	5
7	Dan'l Whiddon	2
8	Harry Hawke	5
Winners

 

[Yweiss0]

Its not working..
Its always write a comment with 0 and it doesn't show me the names

 

[Peter_SSs]

Its not working..
Its always write a comment with 0 and it doesn't show me the names

Well, apart from not being able to see the comment in my screen shot, you can see that it is returning the names, so there must be something different about your setup or implementation.

Try adding the blue line where shown

Application.Volatile
c1 = rng.Columns(1).Address(0, 0)

What Excel version & operating system are you using?

Have you changed my code in any way or merged it with any other code?

Is your layout actually different to this sample?

Try setting up a whole new workbook with just that exact same sample data, put the function (including the extra line mentioned above) in a standard module in that workbook and enter the formula exactly as I have done and see if you can replicate my success with that data.

 

[Yweiss0]

first of all thanks for your help' the only thing is -
I copied and pasted your code and its working only if i use the UDF in the same sheet of the value tables.
I forgot to mention that my name and winning table is not in the same sheet that i want to use the formula ..

so my table with the names and winnings number is in sheet1 and i want to use the UDF in sheet2
how can it be done?

i'm using Excel 2016

 

[Peter_SSs]

I forgot to mention that my name and winning table is not in the same sheet that i want to use the formula ..

so my table with the names and winnings number is in sheet1 and i want to use the UDF in sheet2

Yes, that will make a difference.
Try this one. Use in Sheet2 like this
=Winners(Sheet1!A2:B8)

Function Winners(rng As Range) As String
  Dim Mx As Double
  Dim c1 As String, c2 As String
  
  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 & "|", "")

  With Application.Caller.Parent.Cells(Application.Caller.Row, Application.Caller.Column)
    If Not .Comment Is Nothing Then .Comment.Delete
    .AddComment.Text CStr(Mx)
  End With
End Function

 

[shg]

Or

c1 = rng.Columns(1).Address(External:=True)

 

[Peter_SSs]

Or

c1 = rng.Columns(1).Address(External:=True)

Cheers shg, thanks for the reminder.