The question you posed belongs to the class of questions for which a Top
N list must be built...

One option is to run a pivot table, another to apply a formula system.
In what follows, I'll take up the latter route.

Let G29:G41 house the sample you provided with the label State in G29.

In H29 enter the label: Freq

In H30 enter & copy down:

=IF(ISNUMBER(MATCH(G30,$G$29:G29,0)),"",COUNTIF($G $30:$G$41,G30))

In I29 enter the label: Rank

In I30 enter & copy down:

=IF(N(H30),RANK(H30,$H$30:$H$41)+COUNTIF($H$30:H30 ,H30)-1,"")

In J27 enter the N of Top N: 3

J28:

=MAX(IF(INDEX(H30:H41,MATCH(J27,I30:I41,0))=H30:H4 1,I30:I41))-J27

which you need to confirm with control+shift+enter instead of the usual
enter.

The latter formula determines the ties of the Nth (3rd) value.

In J29 enter the label: Top N List
and in K29 the label: Top N Freq (or some such)

In J30 enter:

=IF(ROW()-ROW(J$30)+1<=$J$27+$J$28,INDEX(G$30:G$41,MATCH(ROW ()-ROW(J$30)+1,$I$30:$I$41,0)),"")

which you copy across to K30 then down:

The area in J:K from J30 on will show the Top 3 states with associated
occurrence frequencies you're after.


Saadi wrote:
Hi guys, I am facing a problem to find out the maximum number of Text
Occurrence in a cloumn.

Suppose I have data in State filed like this in G Column

G
State
CA
CA
FL
GA
GA
CO
CA
FL
IL
FL
WA
FL

Now I want to Show top 3 states in column G30, and H30 automatically. e.g.

FL 4
CA 3
GA 2

so the problem is this how do I check the Max number of occurence of a state
and print it and its number in respective cells.

thanks in advance,

Saddat Sarfraz
Saadi