How to return duplicate max value

Y

Yweiss0

New Member
Joined
Aug 3, 2013
Messages
46
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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Yweiss0 said:
I have in column A numbers of winning in a game
And in column B the names of each player
=INDEX($A$1:$A$16,MATCH(MAX($B$1:$B$16),$B$1:$B$16,0))
And its works.
Click to expand...
with this description your formula shouldn't work.
 
Upvote 0
How about PowerQuery?
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"Number"},#"Table1 (2)",{"Max"},"Table1 (2)",JoinKind.RightOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Table1 (2)"})
in
    #"Removed Columns"[/SIZE]


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]name[/td][td=bgcolor:#5B9BD5]Number[/td][td][/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]Number[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mark[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John[/td][td]
5​
[/td][td][/td][td]Sam[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jim[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Trevor[/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mikael[/td][td]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Andy[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
First of all thanks for the quick response!
My only problem is that I really need it to be in the same cell, I mean all the names in the same cell. Is it possible?
And also for what I know for power query you need to download an addon and i cant download it on the computer that i gonna do the excel file
 
Last edited:
Upvote 0
something like this?


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]name[/td][td=bgcolor:#5B9BD5]Number[/td][td][/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]Number[/td][td=bgcolor:#70AD47]Max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mark[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]Mark[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]John[/td][td]
5​
[/td][td][/td][td]John[/td][td]
5​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jim[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td=bgcolor:#E2EFDA]Jim[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Trevor[/td][td]
2​
[/td][td][/td][td]Trevor[/td][td]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td=bgcolor:#E2EFDA]Sam[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mikael[/td][td]
4​
[/td][td][/td][td]Mikael[/td][td]
4​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Andy[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td=bgcolor:#E2EFDA]Andy[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
 
Upvote 0
If you interested in a UDF
Code: 
Function GetMax(rng As Range) As String
   Dim Mx As Double
   Dim Cl As Range
   
   Mx = Application.Max(rng.Offset(, 1))
   For Each Cl In rng
      If Cl.Offset(, 1) = Mx Then
         If GetMax = "" Then GetMax = Cl Else GetMax = GetMax & ", " & Cl
      End If
   Next Cl
End Function


Excel 2013/2016
ABC
2Tom Pearce1Peter Davy, Harry Hawke
3Bill Brewer2
4Jan Stewer3
5Peter Gurney4
6Peter Davy5
7Dan'l Whiddon2
8Harry Hawke5
vdr
Cell Formulas
RangeFormula
C2=GetMax(A2:A8)
 
Upvote 0
Yweiss0 said:
And also for what I know for power query you need to download an addon and i cant download it on the computer that i gonna do the excel file
Click to expand...
all depends on your excel version. it's basic info you should show in the first post. if it is 2016 or higher - PowerQuery is built-in.

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]name[/td][td=bgcolor:#5B9BD5]Number[/td][td][/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mark[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]John, Sam[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John[/td][td]
5​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jim[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Trevor[/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mikael[/td][td]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Andy[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
Thanks very much! Its great!
Just from curiosity is there any way without UDF?
and another thing, how can i make a comment or mouseover message when i will select the cell C2 or mouseover this cell with the number of winnings? So in the cell i have the names like you did but when i mouseover it i will have a tooltip saying total winnigs: 5.
And obviously if the number of winning change the tooltip text/comment text will also change
 
Upvote 0
Whilst it's probably possible to do with a formula, I wouldn't now how.
This will add a comment to the cell, showing the Max value
Code: 
Function GetMax(rng As Range, Cll As Range)
   Dim Mx As Double
   Dim Cl As Range
   
   Mx = Application.Max(rng.Offset(, 1))
   For Each Cl In rng
      If Cl.Offset(, 1) = Mx Then
         If GetMax = "" Then GetMax = Cl Else GetMax = GetMax & ", " & Cl
      End If
   Next Cl
   If Not Cll.Comment Is Nothing Then Cll.Comment.Delete
   Cll.Addcomment.Text CStr(Mx)
End Function


Excel 2013/2016
ABC
2Tom Pearce1Peter Davy, Harry Hawke
3Bill Brewer2
4Jan Stewer3
5Peter Gurney4
6Peter Davy5
7Dan'l Whiddon2
8Harry Hawke5
vdr
Cell Formulas
RangeFormula
C2=GetMax(A2:A8,C2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Multiple user input value reference
Replies
6
Views
124
Hapakaya
H
N
  • Question Question
Auto Sort for Leaderboard
Replies
0
Views
564
NTLKnight
N
N
  • Question Question
Vlookup with max formula
Replies
1
Views
363
Fluff
Fluff
T
  • Solved
Repeat names in a list by number of times in a cell
Replies
3
Views
419
Fluff
Fluff
M
  • Question Question
Trouble with a formula returning duplicate values
2
Replies
19
Views
964
Cubist
Cubist

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top