return text value found most frequently in a column
 

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

return text value found most frequently in a column
 

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
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

return text value found most frequently in a column
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"globetrotter" wrote:
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

return text value found most frequently in a column
 

Try this *none array entered*

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


"globetrotter" wrote:

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

return text value found most frequently in a column
 

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

globetrotter

"Teethless mama" wrote:

Try this *none array entered*

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


"globetrotter" wrote:

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

return text value found most frequently in a column
 

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

"T. Valko" wrote:

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
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

return text value found most frequently in a column
 

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

"T. Valko" wrote:

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
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

return text value found most frequently in a column
 

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)

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

"T. Valko" wrote:

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
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

return text value found most frequently in a column
 

Build up the formula from its elements to see intermediate results and
understand its workings


+-------------------------------------------------------------------+
|Filename: ArrayFormula.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=72|
+-------------------------------------------------------------------+

--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60302