Display text with highest frequency in range

J

JPM

Active Member
Joined
Aug 1, 2002
Messages
409
Office Version
  1. 365
Platform
  1. Windows
I have a range D2:D2500 with various names of clients. I am trying to figure out which one appears the most time.

I am able to figure out the occurrence of the repetition by using the function: =MODE(COUNTIF(D2:D2500,D2:D2500)) but I am unable to get the name to display.

Does anyone have any suggestions? A VBA solution would be preferred, but a function will work too.

Thanks
 
Sort by date Sort by votes

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Haseeb Avarakkan said:
You need to press Control+Shift+Enter, not just Enter

Also try,

=INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D2500)),COUNTIF(D2:D2500,D2:D2500),0))

Confirmed with Control+Shift+Enter, not just Enter.
Click to expand...


Hi, thanks for the is it worked a treat , what i need to do now is count the maximum number of times a name appears with a specific character and display the one that occurs the most.

I have counted the names in column D and displayed the one with highest frequency using
=INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D2500)),COUNTIF(D2:D2500,D2:D2500),0))

now using the same data i want to display the name with a ! at the end with highest frequency. is that possible ?
E.g
Bob
Bob
Steve!
Steve
Bob!
Dan
Dan!
bob!
Bob!
[TABLE="width: 120"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl70, width: 120"]need to count the same data but display the name that occurs most often that contains a ! at the end
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
eviljimmy said:
Hi, thanks for the is it worked a treat , what i need to do now is count the maximum number of times a name appears with a specific character and display the one that occurs the most.

I have counted the names in column D and displayed the one with highest frequency using
=INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D2500)),COUNTIF(D2:D2500,D2:D2500),0))

now using the same data i want to display the name with a ! at the end with highest frequency. is that possible ?
E.g
Bob
Bob
Steve!
Steve
Bob!
Dan
Dan!
bob!
Bob!
[TABLE="width: 120"]
<TBODY>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl70, width: 120"]need to count the same data but display the name that occurs most often that contains a ! at the end
[/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...

Apparently, you want to consider solely the items which end with a ! sign. For the following exhibit...

[TABLE="width: 53"]
<COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><TBODY>[TR]
[TD="class: xl63, width: 70, bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Steve![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Steve[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bob![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]bob![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bob![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
</TBODY>[/TABLE]

we should have Bob! as result, not Dan. If so:

Control+shift+enter, not just enter:
Rich (BB code): 
=INDEX(D2:D13,MATCH(MAX(FREQUENCY(IF(D2:D13<>"",
  IF(RIGHT(D2:D13)="!",MATCH(D2:D13,D2:D13,0))),ROW(D2:D13)-ROW(D2)+1)),
  FREQUENCY(IF(D2:D13<>"",IF(RIGHT(D2:D13)="!",MATCH(D2:D13,D2:D13,0))),
  ROW(D2:D13)-ROW(D2)+1),0))

If you need to run many instances of this formula, we can avoid computing the FREQUENCY bit twice.
 
Upvote 0
Aladin Akyurek said:
Apparently, you want to consider solely the items which end with a ! sign. For the following exhibit...

[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl63, width: 70, bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bob
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Steve![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Steve[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bob![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]bob![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Bob![/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Dan[/TD]
[/TR]
</tbody>[/TABLE]

we should have Bob! as result, not Dan. If so:

Control+shift+enter, not just enter:
Rich (BB code): 
=INDEX(D2:D13,MATCH(MAX(FREQUENCY(IF(D2:D13<>"",
  IF(RIGHT(D2:D13)="!",MATCH(D2:D13,D2:D13,0))),ROW(D2:D13)-ROW(D2)+1)),
  FREQUENCY(IF(D2:D13<>"",IF(RIGHT(D2:D13)="!",MATCH(D2:D13,D2:D13,0))),
  ROW(D2:D13)-ROW(D2)+1),0))

If you need to run many instances of this formula, we can avoid computing the FREQUENCY bit twice.
Click to expand...


Hi,
Thank you so much for your help
it is working :)
How does it handle ties ? ( if a name appears the same number of times and that is the max value , does it just display the first one on the list that is at that frequency)

would the same formula work for the min frequency of names containing a !? ( change max to min ?)
 
Upvote 0
eviljimmy said:
Hi,
Thank you so much for your help
it is working :)
Click to expand...

You are welcome.

How does it handle ties ? ( if a name appears the same number of times and that is the max value , does it just display the first one on the list that is at that frequency)

would the same formula work for the min frequency of names containing a !? ( change max to min ?)
Click to expand...

Let's use the following small function in order to avoid calculating the same thing multiple times...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

which you can add to your workbook as a module using Alt+F11.

Once this is one...

D2:D13 houses the data:

[TABLE="width: 53"]
<COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><TBODY>[TR]
[TD="class: xl65, width: 70, bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Steve![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Steve[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bob![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dan[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dan![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]bob![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bob![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dan![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dan![/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dan[/TD]
[/TR]
</TBODY>[/TABLE]

J2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX($D$2:$D$13,SMALL(IF(V(FREQUENCY(IF($D$2:$D$13<>"",
  IF(RIGHT($D$2:$D$13)="!",MATCH($D$2:$D$13,$D$2:$D$13,0))),
  ROW($D$2:$D$13)-ROW($D$2)+1))=MAX(V()),ROW($D$2:$D$13)-ROW($D$2)+1),
  ROWS($J$2:J2))),"")
Rich (BB code):
 
Upvote 0
Hey thanks again for your help,
I have got myself stuck on the VB module , I'm not sure how to make it work - or wha ti am lookign for with the new forumla
is it just a different way of doing what you had already?
or does it handle ties , min frequencies?

Cheers
Jimmy
 
Upvote 0
eviljimmy said:
Hey thanks again for your help,
I have got myself stuck on the VB module , I'm not sure how to make it work - or wha ti am lookign for with the new forumla
is it just a different way of doing what you had already?
or does it handle ties , min frequencies?

Cheers
Jimmy
Click to expand...

Have you installed the vba code?
 
Upvote 0
eviljimmy said:
thats where i am stuck , not sure how to do that or tell if its working.
Click to expand...

Hit the Alt and F11 keys at the same time.
Activate the Insert | Module options from the menu.
Copy the code text and paste it in the empty pane on the right.
Acivate the File | Close and Return to Microsoft Excel options from the menu.

Now the formula with the V function will turn the desired results as advertised.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
664
felixstraube
F
E
  • Solved
Syntax problem with SumProduct and range?
Replies
4
Views
247
Peter_SSs
Peter_SSs
S
  • Solved
Gathering Data From Multiple Files in Same Folder in Particular Range
9 10 11
Replies
105
Views
2K
shah0101
S
G
  • Solved
Highlight text in column 1 based on a range of words* in column 2
Replies
6
Views
221
Fluff
Fluff
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
593
StephenCrump
S

Forum statistics

Threads
1,224,518
Messages
6,179,248
Members
452,900
Latest member
LisaGo

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