Sorting by number of ocurrences!

[tbalza]

Hi, i've looked all over google but i can't solve this:

I have a list with independent text strings that are randomly repeated,
and i want to sort them from the most common to least.

For Example:

Column A
---------
Dogs
Cats
Elephants
Dogs
Dogs
Cats
...

What i would like to get is (from the column above):
Dogs (whith 3 ocurrences)
Cats (whith 2 ocurrences)
Elephants (with 1 occurence)

I don't want to do it manually, i.e. having to type "Dogs" or "Cats"
anywhere; i just a formula that looks at the whole list and determine
which is the most common, which is second most common and so on...

I found something that finds only the most common, perhaps that's
start:

-Most Common String In A Range-
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

(where "Rng" is the range, in this case Column A)

Is there any way to tweak that, so it *sorts the list by number of
ocurrences?*

thanks in advance,
T.Balza
tomas {D0T} balza {AT} gmail {D0T} com

 

[Guest]

Hi

I would be tempted to use a pivot table. For an introduction to these, have
a look here:
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Hope this helps.
Andy.

 

[Bob Tarburton]

If your list starts in call A1

In B1 >> =IF(MATCH(A1,A:A,0)=ROW(),COUNTIF(A:A,A1)+1/(ROW()+1),0)

In C1 >> =RANK(B1,B:B,0)

In D1 >> =IF(C1<=COUNTIF(B:B,">0"),INDEX(A:A,MATCH(ROW(),C:C,0))&"
("&TRUNC(INDEX(B:B,MATCH(ROW(),C:C,0)),0)&" occurances","")

Copy B11 down as far as the list or until you get blanks in column D

If your list starts below column 1, you will need to both occurances of
ROW() in D1 so that the first one yields 1, such as ROW()-3 if you start in
column 4 (or ROW()-ROW(D$4)+1 again if start in row 4, which allows you to
insert/delete rows)

Also If your list starts below column 1, make sure nothing in your list is
typed in column A above your list or the first occurance of of ROW() in B1
allso needs adjusting.

I'm sure there are more elegant ways of doing this, but this one works.

 

[tbalza]

i'm infinitely grateful, thanks guys.

 

[tbalza]

the Pivot Tables functions solved it, simpy go to Data > Pivot Table and
drop the data into the apropiate table.

thanks again guys