Counting Text Cells

[Big Ben]

Hi Guys,

How can I determine the most common occurence of a word in a lis
formatted as text in Column A. I would like a function that simpl
returns the word that occurs the most to appear in cell D1. Also, i
there are two words that occur the same number of times in this list,
would like both words to be shown (one in D1, one in D2).

Any suggestions?

Thanks,

Be

 

[Frank Kabel]

Hi
to get the most frequent text entry try the following array formula
(entered with CTRL+SHIFT+ENTER):
=INDEX($B$1:$B$20,MODE(MATCH(IF($B$1:$B$20<>"",$B$1:$B$20),IF($B$1:$B$2
0<>"",$B$1:$B$20),0)))

 

[Big Ben]

Hi Frank,

I can't seem to get this to work. The formula returns a value of 0.
did do the formula as an array. I am assuming from your formula tha
the list is in column B? I put the formula in cell D1 but no luck. A
I missing something?

Thanks,

Be

 

[Frank Kabel]

Hi
ben: Yes the formula assumes you have the values in column B. You have
to enter this formula as array formula (with CTRL+SHIFT+ENTER).

 

[Big Ben]

Hi Frank,

I still can't seem to get this working. I have the formula in cell D
as an array (CTRL + SHIFT + ENTER) and my list is in column B. I a
going to restate my problem with an example: In column B I have m
fictionary list of names as follows (The list will always be changin
with no fixed set of names)

Column B
Jim
Bob
Mary
Jim
Jim
Mary
Jane

Since the name "Jim" occurs three times, I would like a formula in D
to display the word "Jim" in cell D1. I am sure you understand m
problem and you have the correct formula, I just can't seem to make i
work.

This is what my formula looks like:

{=INDEX($B$1:$B$20,MODE(MATCH(IF($B$1:$B$20<>"",$B$1:$B$20),IF($B$1:$B$20<>"",$B$1:$B$20),0)))}

Sorry to keep bothering you with this problem!

Thanks.

Be

 

[Dave Peterson]

Do you have gaps in your data?

If no, you can define a dynamic range that will grow/contract with your data:

Debra Dalgleish has some nice notes at:
http://www.contextures.com/xlNames01.html#Dynamic

I created a name called myList using this formula:
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

Then I used this in D1:
=INDEX(myList,MODE(MATCH(myList,myList,0)))