Data filtering(?) Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BryGuy77 - 27 May 2008 04:02 GMT

Hi all,

How can I list the most common word in a given range? I have office 2007, if
this matters. The range could all potentially carry the same value, or there
could be blank lines as well, so considering the blank line issue would be
appreciated. Thanks for the help!

Reply to this Message

T. Valko - 27 May 2008 04:26 GMT

Try this array formula** :

=INDEX(A1:A20,MODE(IF(A1:A20<>"",MATCH(A1:A20,A1:A20,0))))

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

Signature

Biff
Microsoft Excel MVP

> Hi all,
>
[quoted text clipped - 4 lines]
> could be blank lines as well, so considering the blank line issue would be
> appreciated. Thanks for the help!

Reply to this Message

Dave Curtis - 27 May 2008 09:59 GMT

Hi,

For some reason, using MODE doesn't work for me with text, perhaps because
I'm using Excel 2003?

This similar array formula seems to do the trick.

{=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20),0))
}

Dave

url:http://www.ureader.com/msg/103456964.aspx

Reply to this Message