Sorting by number of ocurrences!

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 (with 3 ocurrences)
Cats (with 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 a 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

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.

"tbalza" <[email protected]> wrote in
message news:[email protected]...
>
>
> 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
>
>
> --
> tbalza
> ------------------------------------------------------------------------
> tbalza's Profile:
> http://www.excelforum.com/member.php...o&userid=28080
> View this thread: http://www.excelforum.com/showthread...hreadid=507707
>

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 B1:D1 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" <[email protected]> wrote in
message news:[email protected]...
>
>
> 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
>
>
> --
> tbalza
> ------------------------------------------------------------------------
> tbalza's Profile:
> http://www.excelforum.com/member.php...o&userid=28080
> View this thread: http://www.excelforum.com/showthread...hreadid=507707
>

i'm infinitely grateful, thanks guys.

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

thanks again guys