help with text sorting

J

jjcx

:confused: i am trying to find a formula in excel that will find the
most frequent word from a list of words. The MODE function only works
on numbers and i wondered if any formulas worked on text. PLZ HELP -
THNX.
 
M

Martin P

With your list in column A, enter =COUNTIF(A:A,A1) in B1 and copy down.
C1:
=MAX(B:B)
D1:
=MATCH(C1,B:B,0)
E1:
=INDEX(A:A,D1)
E1 gives the most frequent word.
 
R

Ragdyer

This will work for numbers *and/or* text!

This also counts empty cells as 0's, so if they (empty cells) outnumber your
data values, you'll get a return of "0".

=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))
 
J

jjcx

I tried the formula given above but still cant get the cell to return
anything but #N/A. Thnx 4 ure help if you have any ideas whats wrong
plz help.

THNX
JJCX.
 
R

RagDyeR

#N/A means you *don't* have more then 1 of each value!

Try manually keying in some duplicate values and see what happens.

Be aware:
JJCX
and
JJ CX
Are *not* a match and would not count as such.

If data is imported, there could very well be invisibly imbedded characters
which preclude an exact match.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I tried the formula given above but still cant get the cell to return
anything but #N/A. Thnx 4 ure help if you have any ideas whats wrong
plz help.

THNX
JJCX.
 
Top