Returning the most duplicated item
 

Let's say I have a column with a bunch of names in it, and I want a function
that will return the name that shows up the most often (the mode I guess).
How do I do that?

I know how to do it if the items are numerical data, but how do I do it with
text?

For example, suppose this is a column:

Smith
Johnson
Smith
Roberts
Johnson
Mulligan
Roberts
Smith
Williams
Williams
Smith

I want a function that will return "Smith" as the name that appears the most
often.

Returning the most duplicated item
 

Hi

Assuming that your data is in A1:A11, then array enter (ctrl, shift,
enter) the formula


Code:
--------------------
=INDEX(A:A,MIN(IF(COUNTIF(A1:A11,A1:A11)=MAX(COUNT IF(A1:A11,A1:A11)),ROW(A1:A11),"")))
--------------------


rylo


--
rylo
------------------------------------------------------------------------
rylo's Profile: http://www.thecodecage.com/forumz/member.php?userid=28
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33152

Returning the most duplicated item
 

Assuming no empty cells.

Array entered** :

=INDEX(A1:A11,MODE(MATCH(A1:A11,A1:A11,0)))

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

--
Biff
Microsoft Excel MVP


"Joe Lewis" wrote in message
...
Let's say I have a column with a bunch of names in it, and I want a
function
that will return the name that shows up the most often (the mode I guess).
How do I do that?

I know how to do it if the items are numerical data, but how do I do it
with
text?

For example, suppose this is a column:

Smith
Johnson
Smith
Roberts
Johnson
Mulligan
Roberts
Smith
Williams
Williams
Smith

I want a function that will return "Smith" as the name that appears the
most
often.

Returning the most duplicated item
 

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

Just press Enter


"Joe Lewis" wrote:

Let's say I have a column with a bunch of names in it, and I want a function
that will return the name that shows up the most often (the mode I guess).
How do I do that?

I know how to do it if the items are numerical data, but how do I do it with
text?

For example, suppose this is a column:

Smith
Johnson
Smith
Roberts
Johnson
Mulligan
Roberts
Smith
Williams
Williams
Smith

I want a function that will return "Smith" as the name that appears the most
often.