Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting random occurrences
I have a spread sheet which has random but repeating occurrences of alpha
numerics or alpha alphas - say 5t or fe or d4 etc etc. I want count the top 5 repeating a/n or a/a's without having to go to sort / autosort clicking on each a/a or a/n to view the repeats. is there a way of doing this please Bill |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting random occurrences
The easiest way is to create a Pivot Table and then sort by the Count
descending. The following will get you results without sorting, but it needs extra cells: Lets say in column B we have dog horse bird angel dog horse angel cat cat horse horse horse horse cat angel horse angel angel microbe angel angel cat fly angel cat fly cat dog fly horse angel bird angel angel cat cat dog bird angel bird angel angel dog fle horse dog fle angel angel angel In A1 enter: =IF(COUNTIF($B$1:B1,B1)=1,COUNTIF($B$1:$B$50,B1)," ") and then copy down thru A50. We will see: 6 dog 9 horse 4 bird 17 angel dog horse angel 8 cat cat horse horse horse horse cat angel horse angel angel 1 microbe angel angel cat 3 fly angel cat fly cat dog fly horse angel bird angel angel cat cat dog bird angel bird angel angel dog 2 fle horse dog fle angel angel angel There is a once-only count associated with each repeating item. Finally in C1 enter: =LARGE($A$1:$A$50,ROW()) and copy down thru C5 In D1 enter: =VLOOKUP(C1,A$1:B$50,2,0) and copy down thru D5 In C1 thru D5 we will have: 17 angel 9 horse 8 cat 6 dog 4 bird The five top repeats and how often they occurred. If you get another response that simpler or find the Pivot Table acceptable, ignore this post. -- Gary''s Student - gsnu200720 "billy_bags" wrote: I have a spread sheet which has random but repeating occurrences of alpha numerics or alpha alphas - say 5t or fe or d4 etc etc. I want count the top 5 repeating a/n or a/a's without having to go to sort / autosort clicking on each a/a or a/n to view the repeats. is there a way of doing this please Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurrences on a particular date | Excel Worksheet Functions | |||
Counting occurrences over range of sheets | Excel Worksheet Functions | |||
Counting number of occurrences | Excel Worksheet Functions | |||
counting occurrences in a range | Excel Discussion (Misc queries) | |||
Counting occurrences of multiple conditions | Excel Discussion (Misc queries) |