Sorting by number of ocurrences!
 

:confused:
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

Sorting by number of ocurrences!
 

Hi

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

Hope this helps.
Andy.

"tbalza" wrote in
message ...

:confused:
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

Sorting by number of ocurrences!
 

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" wrote in
message ...

:confused:
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

Sorting by number of ocurrences!
 

i'm infinitely grateful, thanks guys.:cool:


--
tbalza
------------------------------------------------------------------------
tbalza's Profile: http://www.excelforum.com/member.php...o&userid=28080
View this thread: http://www.excelforum.com/showthread...hreadid=507707

Sorting by number of ocurrences!
 

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

thanks again guys


--
tbalza
------------------------------------------------------------------------
tbalza's Profile: http://www.excelforum.com/member.php...o&userid=28080
View this thread: http://www.excelforum.com/showthread...hreadid=507707