software

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 quot;Dogsquot; or quot;Catsquot;
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 quot;Rngquot; 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: www.excelforum.com/member.php...oamp;userid=28080
View this thread: www.excelforum.com/showthread...hreadid=507707Hi

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

Hope this helps.
Andy.

quot;tbalzaquot; gt; wrote in
message ...
gt;
gt;
gt; Hi, i've looked all over google but i can't solve this:
gt;
gt; I have a list with independent text strings that are randomly repeated,
gt; and i want to sort them from the most common to least.
gt;
gt; For Example:
gt;
gt; Column A
gt; ---------
gt; Dogs
gt; Cats
gt; Elephants
gt; Dogs
gt; Dogs
gt; Cats
gt; ..
gt;
gt; What i would like to get is (from the column above):
gt; Dogs (whith 3 ocurrences)
gt; Cats (whith 2 ocurrences)
gt; Elephants (with 1 occurence)
gt;
gt; I don't want to do it manually, i.e. having to type quot;Dogsquot; or quot;Catsquot;
gt; anywhere; i just a formula that looks at the whole list and determine
gt; which is the most common, which is second most common and so on...
gt;
gt; I found something that finds only the most common, perhaps that's
gt; start:
gt;
gt; -Most Common String In A Range-
gt; =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))
gt;
gt; (where quot;Rngquot; is the range, in this case Column A)
gt;
gt; Is there any way to tweak that, so it *sorts the list by number of
gt; ocurrences?*
gt;
gt; thanks in advance,
gt; T.Balza
gt; tomas {D0T} balza {AT} gmail {D0T} com
gt;
gt;
gt; --
gt; tbalza
gt; ------------------------------------------------------------------------
gt; tbalza's Profile:
gt; www.excelforum.com/member.php...oamp;userid=28080
gt; View this thread: www.excelforum.com/showthread...hreadid=507707
gt;
If your list starts in call A1

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

In C1 gt;gt; =RANK(B1,B:B,0)

In D1 gt;gt; =IF(C1lt;=COUNTIF(B:B,quot;gt;0quot;),INDEX(A:A,MATCH(ROW(),C: C,0))amp;quot;
(quot;amp;TRUNC(INDEX(B:B,MATCH(ROW(),C:C,0)),0)amp;quot; occurancesquot;,quot;quot;)

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.
quot;tbalzaquot; gt; wrote in
message ...
gt;
gt;
gt; Hi, i've looked all over google but i can't solve this:
gt;
gt; I have a list with independent text strings that are randomly repeated,
gt; and i want to sort them from the most common to least.
gt;
gt; For Example:
gt;
gt; Column A
gt; ---------
gt; Dogs
gt; Cats
gt; Elephants
gt; Dogs
gt; Dogs
gt; Cats
gt; ..
gt;
gt; What i would like to get is (from the column above):
gt; Dogs (whith 3 ocurrences)
gt; Cats (whith 2 ocurrences)
gt; Elephants (with 1 occurence)
gt;
gt; I don't want to do it manually, i.e. having to type quot;Dogsquot; or quot;Catsquot;
gt; anywhere; i just a formula that looks at the whole list and determine
gt; which is the most common, which is second most common and so on...
gt;
gt; I found something that finds only the most common, perhaps that's
gt; start:
gt;
gt; -Most Common String In A Range-
gt; =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))
gt;
gt; (where quot;Rngquot; is the range, in this case Column A)
gt;
gt; Is there any way to tweak that, so it *sorts the list by number of
gt; ocurrences?*
gt;
gt; thanks in advance,
gt; T.Balza
gt; tomas {D0T} balza {AT} gmail {D0T} com
gt;
gt;
gt; --
gt; tbalza
gt; ------------------------------------------------------------------------
gt; tbalza's Profile:
gt; www.excelforum.com/member.php...oamp;userid=28080
gt; View this thread: www.excelforum.com/showthread...hreadid=507707
gt;

i'm infinitely grateful, thanks guys.--
tbalza
------------------------------------------------------------------------
tbalza's Profile: www.excelforum.com/member.php...oamp;userid=28080
View this thread: www.excelforum.com/showthread...hreadid=507707
the Pivot Tables functions solved it, simpy go to Data gt; Pivot Table and
drop the data into the apropiate table.

thanks again guys--
tbalza
------------------------------------------------------------------------
tbalza's Profile: www.excelforum.com/member.php...oamp;userid=28080
View this thread: www.excelforum.com/showthread...hreadid=507707