Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 (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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by number of ocurrences!
i'm infinitely grateful, thanks guys. -- tbalza ------------------------------------------------------------------------ tbalza's Profile: http://www.excelforum.com/member.php...o&userid=28080 View this thread: http://www.excelforum.com/showthread...hreadid=507707 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Sorting Numbers with Multiple Decimals (cont.) | Excel Discussion (Misc queries) | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) |