counting random occurrences

[Guest]

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

 

[Guest]

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.