Display text with highest frequency in range

[JPM]

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

 

[mikerickson]

Perhaps something like

=INDEX(D:D, 10000*MOD(MAX(COUNTIF(D2:D2500,D2:D2500)+(ROW(D2:D2500)/10000)),1), 1)

which should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[JPM]

That almost works, but it cuts off part of the data after the sixth character for some reason.

 

[mikerickson]

Have you tried widening the column in which the formula resides?

 

[JPM]

Actually, I just figured out what the problem is, it always just shows the value in D1.

 

[Haseeb Avarakkan]

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.

 

[JPM]

Haseeb,
I am using Ctrl+shift+enter for the array formula, but it is still not working. Your formula always shows the name on the top of the data.

 

[Aladin Akyurek]

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

E1: #Most Freq#

E2, control+shift+enter, not just enter, and copy down:

If on 2007 or later...

=IFERROR(INDEX($D$2:$D$11,MODE(IF(COUNTIF($D$2:$D$11,$D$2:$D$11)>1,
   IF(ISNA(MATCH($D$2:$D$11,$N$1:N1,0)),MATCH($D$2:$D$11,$D$2:$D$11,0))))),"")

Otherwise:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($D$2:$D$11,
   MODE(IF(COUNTIF($D$2:$D$11,$D$2:$D$11)>1,IF(ISNA(MATCH($D$2:$D$11,$E$1:E1,0)),
     MATCH($D$2:$D$11,$D$2:$D$11,0)))))))

 

[JPM]

Thanks everybody who helped me. I finally got it working now.

It is amazing how complicated it is to get this working for text when a simple MODE function works for numbers.

Now, does anyone have a VBA solution as well?

 

[T. Valko]

Thanks everybody who helped me. I finally got it working now.

It is amazing how complicated it is to get this working for text when a simple MODE function works for numbers.

It may be as easy as this.

Array entered**:

=INDEX(range,MODE(MATCH(range,range,0)))

That assumes there are no empty cells within the range and that there is at least one entry that has multiple instances (mode).

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.