return text value found most frequently in a column

[globetrotter]

I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter

 

[T. Valko]

Try this array formula** :

=INDEX(A1:A367,MODE(IF(A1:A367<>"",MATCH(A1:A367,A1:A367,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Max]

Another option, array-entered* in say, B2:
=INDEX(A2:A400,MATCH(MAX(COUNTIF(A2:A400,A2:A400)),COUNTIF(A2:A400,A2:A400),0))

*Press CTRL+SHIFT+ENTER to confirm the formula

In the event of ties in the max counts, then only the 1st "max" name (the
one higher up) will be returned

 

[Teethless mama]

Try this *none array entered*

=INDEX(A1:A367,MODE(INDEX(MATCH(A1:A367,A1:A367,),)))

 

[globetrotter]

Thanks for the helpful and quick responses. I got just what I was looking for.

globetrotter

 

[Vusal]

can u explain how formula works. I know all of these formulas. Yes it it
works, but i cannot understand 'em.

 

[Vusal]

can u explain how formula works. I know all of these formulas. Yes it it
works, but i cannot understand 'em.
Write pls (e-mail address removed)

 

[Bob Phillips]

MATCH(A1:A367,A1:A367,0) returns an array of the first row number of each
separate value, so you will get many repetitions per value

As there may be blanks, which return #N/A, this is catered for by

IF(A1:A367<>"",MATCH(A1:A367,A1:A367,0))

so as to get an array with row numbers and FALSE (many functions nicely
ignore FALSE and don't error as they would with #N/A

MODE(IF(A1:A367<>"",MATCH(A1:A367,A1:A367,0)))

then returns the most frequently occurring, or repetitive, value in that
array

and it is passed to INDEX to get the actual value.

QED


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)