Mode of Text....

[cjwenngatz]

I need to pull the most commonly used word out of a data range. In
essence if I have a list of names; Is there any way Excel can pull up
the name that is repeated the most, and how many times it is repeated?
Essentially a mode of text.

 

[Chip Pearson]

Assuming your text values are in A1:A10, insert a new column and enter

=COUNTIF($A$1:$A$10,A1)
in cell B1 and fill down to B10.

Then use the following formula to return the value in column A that occurs
the most frequently. In case of a tie (2 different strings occur the same
number of time), the first (lowest row number) item will be returned.

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0),0)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

 

[Harlan Grove]

(e-mail address removed) wrote...

I need to pull the most commonly used word out of a data range. In
essence if I have a list of names; Is there any way Excel can pull up
the name that is repeated the most, and how many times it is repeated?
Essentially a mode of text.

I think Leo Heuser came up with this.

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