return the string that occurs most often

PeaceMaker

unread,

Dec 28, 1997, 3:00:00 AM12/28/97

to

Hi All:

Is there a function or set of functions which will return a character
string which appears most often in an array? For example, if I have a list
such as:

apple
orange
orange
kiwi
orange
apple

the formula will return 'orange'.

Tod

Jim Rech

unread,

Dec 29, 1997, 3:00:00 AM12/29/97

to

You can do it all in one cell like this:

=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10)))

This formula MUST be array-entered to work, that is, press Ctrl-Shift-Enter
rather than just Enter.

Jim

David Hager

unread,

Dec 29, 1997, 3:00:00 AM12/29/97

to

Try:

=INDEX(range,MATCH(MAX(COUNTIF(range,range)),COUNTIF(range,range),0))

Hold down the Ctrl and Shift keys when entering this formula.

David Hager

PeaceMaker <tod...@flex.net> wrote in article
<01bd13eb$58a829e0$1534...@isd-qa-a30.hous-tx.wang.com>...

PeaceMaker

unread,

Dec 31, 1997, 3:00:00 AM12/31/97

to

>Hi All:
>
>Is there a function or set of functions which will return a character
>string which appears most often in an array? For example, if I have a list
>such as:
>
>apple
>orange
>orange
>kiwi
>orange
>apple
>
>
>the formula will return 'orange'.
>
>Tod

I think I should amend my question after some helpful advise from a
few of you. What I actually need to do is have a formla or other
determine an array based on a given text string, then choose the
string that most frequently appears in a column of that array. Using a
better example:

A B ...... S
vegie other beet
bread other bun
fruit other apple
fruit other peach
fruit other apple
pasta other ravioli

The words in column A will not always be the same and will not always
appear in the same cells, so I can't name a range or reference certain
cells in my formula.

In this example, I want to be able to type the word 'fruit' in a
different cell (on a different worksheet in the workbook) and have a
formula read the word, find in the above example that fruit is
associated with the words apple, peach, and apple in the S column on
the same rows, and then tell me which of the associated words in
column S appears most frequenly. In this case, the answer would be
'apple'.

Thanx for the help so far. Just a few more suggestions and I should
have it.

Tod
apple
orange

PeaceMaker

unread,

Dec 31, 1997, 3:00:00 AM12/31/97

to

>Hi All:
>
>Is there a function or set of functions which will return a character
>string which appears most often in an array? For example, if I have a list
>such as:
>
>apple
>orange
>orange
>kiwi
>orange
>apple
>
>
>the formula will return 'orange'.
>
>Tod

David sent me a possible solution and requested that I post it to this
NG.

He wrote:

Here is a solution, albeit with some
limitations:

=INDEX(range2,MATCH(MODE(IF(CODE(IF(range1="c",range2,0))=48,"",CODE(IF(ran
ge1="c",range2)))),CODE(range2),0))

where range2 is Column S and range1 contains "c". The main limitation
is
that the words in Column S all have to begin
with a different letter.

The COUNTIF function cannot be used in this problem (at least in a
single
formula solution) since it only accepts a range
argument as the 1st argument and not an array.

Again, make sure that you repost this in the newsgroup.

Regards,
David Hager

David Hager

unread,

Dec 31, 1997, 3:00:00 AM12/31/97

to

This formula also will not give a result if there is no real MODE in the
criteria data set.

David Hager

PeaceMaker <tod...@flex.net> wrote in article

<34aaae96...@news.supernews.com>...

Dana De

unread,

Jan 6, 1998, 3:00:00 AM1/6/98

to

When you get this fancy, I believe your best bet is to use a pivot table and
let Excel do most of the work to give you a summary of your data.
You can then use easier Vlookup functions (or whatever) from this smaller
data set !

There are many ways to format the output. One way is to put only Row A & S
in the 'Row' of the pivot table wizard. Do a 'Count of S' in the Data area.
Delete all subtotals for each of the row groups. Also, delete all
Subtotals in the output options.

Now you will find it much easier to work with this data. (again, let Excel
do the hard work.) Hope this helps.

PeaceMaker wrote in message <34aa0473...@news.supernews.com>...

>>Hi All:
>>
>>Is there a function or set of functions which will return a character
>>string which appears most often in an array? For example, if I have a list
>>such as:
>>
>>apple
>>orange
>>orange
>>kiwi
>>orange
>>apple
>>
>>
>>the formula will return 'orange'.
>>
>>Tod

Laurent Longre

unread,

Jan 8, 1998, 3:00:00 AM1/8/98

to tod...@flex.net

Bonjour PeaceMaker,

I've seen David Hager's nice formula.
The following is another possible solution. It's a bit tedious, but it
avoids the limitations caused by the MODE function:

=INDEX(Rge2,MAX(ROW(INDIRECT("1:"&ROWS(Rge1)))*(COUNTIF(Rge2,Rge2)
*(Rge1="fruit")=MAX(COUNTIF(Rge2,Rge2)*(Rge1="fruit")))))
... array entered

Cordialement,
Laurent Longre