Mode of Text....

  • Thread starter Thread starter cjwenngatz
  • Start date Start date
C

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.
 
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)
 
(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.
Click to expand...

I think Leo Heuser came up with this.

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