Formula to show most common text in a range?

[MikeyFB]

Hello all

My aim is to have a forula automatically look at a range and tell me the most common text and then the amount of time the text occured.

I did use a mix of formula last week but left it and have now lost all trains of thought.



Imagine in Collumn A the cells are:
Apple
Bananna
Carrot
Apple
Apple
Carrot


I want the formula to display the word apple as it is the most common.
I then want to be a able to fill down to show Carrot as the second most common and so on.

My formula looks like this at the moment;

{=INDEX(Sheet1!$A$1:$A$500,MODE(IF((Sheet1!$A$1:$A$500<>"")*ISNA(MATCH(Sheet1!$A$1:$A$500,$B$1:$B1,0)),MATCH(Sheet1!$A$1:$A$500,Sheet1!$A$1:$A$500,0))))}

It will show the text with more than one occurance, but not those with a single entry.

Help!

 

[Armando Montes]

Check this:
http://www.mrexcel.com/forum/showthread.php?t=437318
=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))
=IF(COUNTIF(A$2:A2,A2)>1,A2,"")

 

[MikeyFB]

Two things....

1) it assumes there are no empty cells. But i have adapted to fit.
2) It only tells me the most frequent. How do I then do 2nd most frequent, then 3rd etc.

 

[sulakvea]

just out of curiosity - why not use pivot table?

 

[MikeyFB]

You would think that would make sense.

However, a pivot table is too complicated for those who are going to receive the file. The idea is they just have to run a macro.

 

[MikeyFB]

Any Thoughts today?

 

[mikerickson]

This uses names
Name: dataRange
RefersTo: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(""zzz"",Sheet1!$A:$A),1)

Name: OneToN
RefersTo: =ROW(INDEX(Sheet1!$A:$A,1,1):INDEX(Sheet1!$A:$A,ROWS(dataRange),1))

Name: PTen
RefersTo: =10^CEILING(LOG(ROWS(dataRange)+1),1)

Then the CSE formula
{=INDEX(dataRange, PTen*MOD(LARGE(IF(MATCH(dataRange&"", dataRange&"", 0)=OneToN, COUNTIF(dataRange, dataRange&"")+(OneToN/PTen)), ROW(A1)),1),1)}

when entered in a cell with Ctrl-Shift-Enter (Cmd+Return for Mac),
will return the most frequent text in dataRange.

Drag it down one cell and it will return the second most frequent, etc.

The red ROW(A1) indicates which ranking to return.

 

[Mike Szczesny]

Hi Mikey,

Assume a1 to a6 contains a,a,a,b,b,c (going from a1 to a6). Here is a formula to display the items (a, b, or c) with the most frequently occuring, 2nd most occurring, and 3rd most occurring. etc. Place formula in b1

=index(a1:a6,small(if(frequency(if(a1:a6<>"",match(a1:a6,a1:a6,0)),row(a1:a6)-row(a1)+1),row(a1:a6)-row(a1)+1),rows($b$1:b1))). use CSE and copy down. Use absolute references for all ranges. Leave rows(range) portion as is. $b$1:b1 refers to 1 (most frequent), $b$1:b2 refers to 2nd most frequent, etc.


a is the most frequent (qty=3), then b (qty=2), etc
First entry will show most frequently occuring, 2nd occurring, 3rd occuring. The number of items is dependent on the size and number of different items in range.

HTH,
Mike Szczesny

 

[mikerickson]

To explain my solution.
The Named Values
dataRange is a dynamic named range of the data
OneToN is the array of integers from 1 to the number of rows in dataRange.
PTen is the lowest power of 10 that is larger than the number of rows in dataRange.

In this explaination assume dataRange =A1:A89, so OneToN = {1; 2; 3; ...; 89} and PTen = 100

FIRST:
COUNTIF(dataRange, dataRange&"") is the array
{COUNTIF(dataRange, A1);
COUNTIF(dataRange, A2);
COUNTIF(dataRange, A3);
...
COUNTIF(dataRange, A89)}
which lists the number of occurences of each entry in dataRange. Appending OneToN/PTen puts the row index of each element to the right of the decimal point of that count (note that counts are integers).
{COUNTIF(dataRange, A1)+.01;
COUNTIF(dataRange, A2)+.02;
COUNTIF(dataRange, A3)+.03;
...
COUNTIF(dataRange, A89)+.89}

NEXT:
MATCH(dataRange&"", dataRange&"", 0)=OneToN is an array of True/False values such that it is TRUE only if the corresponding entry in dataRange is the first occurrence of that entry in the dataRAnge.

So,
IF(MATCH(dataRange&"", dataRange&"", 0)=OneToN, COUNTIF(dataRange, dataRange&"")+(OneToN/PTen))
is the array from PartOne, with False replacing the Count/RowIndex value of the duplicated entries.

FINALY:
LARGE(IF(...), 1) is the largest of that array. i.e. The largest Count/Rowindex combo.
Similarly LARGE(IF(...), 2) is the second largest.

PTen*MOD(LARGE(IF(...), n)) takes the nth largest count/rowindex and extracts the row index.
INDEX(dataRange, PTen*MOD(LARGE(IF(...), n)), 1) is the n'th most common entry in dataRange.

By dragging INDEX(dataRange, PTen*MOD(LARGE(IF(...), ROW(A1))), 1) down, one set n = 1 and increasing.

 

[LXCXL]

I know this is old but I just wanted to drop past and say thanks to Mike for his formula and explanation. Seriously so sick. Thanks heaps!