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]

Thanks a million!
If i want another UDF but for the MIN thia time it ahould be the same except the line
Mx = Application.Max(rng.Offset(, 1))
That should be
Mn = Application.Min(rng.Offset(, 1))
Am I righy?
And of course to change the UDF name

 

[Fluff]

Yes that's right

 

[Yweiss0]

Great!
The UDF will also work if ny name are in a different sheet?
Can I make =GETMAX(sheet2A1:sheet2A28)?

 

[Fluff]

It needs to be like
=GetMax(Sheet2!A2:A8,G8)
Where the formula is in G8

 

[Yweiss0]

Oh ok thanks, so if i want the names in cell G8 i will write exactly how you wrote.
And last thing...
If i will change the position of the numbers column and ill put it before the names si the names will be in column B and the numbers will be in column A i should do the UDF like this:
Mx = Application.Max(rng.Offset(, -1)
Is that right?
And another thing there's a way to add the UDF to set the width of the cell automatic to length of the text so if there are more then 1 name the width will grow in a way that i will always see all of the names?

 

[Fluff]

For the first part, yes that's right.
For the second part, fraid not. A UDF is in some respects like a formula & cannot change formatting in that way

 

[Yweiss0]

Ok thanks a lot for your help.
About the commet, is there a way to show the comment when i mouseover the cell and not have to select the cell?
Like in data validation when you choose inout message. I want it to show when i mouseover the cell and to disappear when i move the mouse to a different cell. Like a tool tip

 

[shg]

I want it to show when i mouseover the cell and to disappear when i move the mouse to a different cell.

That's the default behavior for a comment.

 

[Yweiss0]

It works thank you!
The only problem is if i change the winning numbers the getmax UDF is doesn't update the values. What I need to change that the vakue in the cell will get update and also the comment will update with the new max number

 

[Peter_SSs]

Just from curiosity is there any way without UDF?

Yes, it just depends which version of Excel you are using and/or how much patience you have to set up the formula.

If you have Excel 2016+, it is easy with the C2 formula. (It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.)

If you have an older Excel version then you could use a structure like D1 & D2. Of course the D2 formula would be very long if you had 100 players (hence my comment about patience ) but if, say, 16 like your post 1 example then it is quite feasible - you just have to keep adding terms like &IF(B8=D$1,", "&A8,"") to the main part of the formula as I have only catered for 7 players.

Excel Workbook
	A	B	C	D
1	Name	Score	Winners	5
2	Tom Pearce	1	Peter Davy, Harry Hawke	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