How-to: Extract, count and rank text occurrences

Hello everyone,

I kindly request assistance with the following Excel problem that's bugging me at the moment. The starting table looks as follows, two roes:

Text A Occurence 1
Text A Occurence 2
Text B Occurence 1
...

I need to do the following in Excel 2010:

1. In column A, identify the most common occurrences of text, the most common minus 1, the most common minus 2 and so on for the first 5 most common texts. The first 5 most common items should be identified.
2. Rank these 5 texts with regard to their frequency.
3. Produce a final output that looks something like this:

Text B 21 times
Text A 12 times
Text C 6 times
...

Note: I do not necessarily need to know the actual number of occurrences, but need to have a text list of the 5 most common strings in ranked order. I know this must probably be done using an interim or helper table, but can't figure out how to do it.

Can someone help?`

Thank you very much!

Bijan

Sure, please find the file enclosed. I would to know:

1. What are the 5 most common media types (column "Medium")
2. Rank these with regard to commonality/frequency of occurence
3. Feed these 5 into a pie diagram that outputs these graphically

There are only 2 types of media, so it is hard to find 5 most common

Into cell E2:

=IFERROR(INDEX(B$2:B$35,MODE(IF(COUNTIF(E$1:E1,B$2:B$35)=0,MATCH(B$2:B$35,B$2:B$35,0)+{0,0}))),"")

and CSE not just Enter. Then copy down 4 times (to get 5 the most common).
Expand range of rows (bolded) as you need.

and into cell F2:

=IF(E2<>"",COUNTIF(B:B,E2),"")

and copy down as above, as many times as you need.

Come on, you can create chart yourself.

Thanks KOKOSEK, thanks Glenn! That was extremely helpful and solved my problem in no time. Thanks!