Top 5 Text Strings from List - Formula advice

[batteredveg]

Hi Folks

Would be grateful of any advice.

I'm trying to generate a 'Top 5' of the most frequently occurring text strings in a column.

In Column A is a list of countries:

	A​
1	Germany
2	United Kingdom
3	Germany
4	Italy
5	United Kingdom
6	United States of America
7	Ireland
	...

<tbody>
</tbody>

In another column, without the use of pivot tables or filters, I'd like to have a Top 5 list which will list, in the correct order, the most frequently occurring text strings, like this:

	C​	D​
1	Top 5	Count
2	Germany	27
3	United Kingdom	23
4	Italy	22
5	United States of America	16
6	France	11

<tbody>
</tbody>

Can anyone suggest a formula which would do the job? Nothing I've found via Google seems to do the job.

Much obliged,

Paul

 

[JackDanIce]

Welcome to the board.

You could use ADVANCED FILTER to create a list of unique values from column A, then next to the unique list, use the COUNTIF function to return the count of the unique values, before sorting them in descending order by count to return the list ranked by count.

I do not think you can do this simply with formulae alone.

 

[ttdk1]

In column A, you have a list if countries from A2 to A10.
In column B, you have have a helper column that display total occurrence each countries appears in the list in column A.
In Column C, you have a list of the top 5 countries with the most occurrences, from highest count to lowest count.
in Column D, you have the total occurrences for each of the countries in the list on Column C.

in B2, copy and drag down the formula

=COUNTIF($A$2:$A$10,A2)

in C2: ctrl-shift-enter, copy and drag down the array formula

=IF(ROWS($C$2:$C2)<=5,INDEX(A$2:A$10,MATCH(1,(B$2:B$10=LARGE(IF(COUNTIF(C$1:C1,A$2:A$10)=0,B$2:B$10),1))*(COUNTIF(C$1:C1,A$2:A$10)=0),0)),"")

in D2, copy and drag down the formula

=IF(C2<>"",COUNTIF($A$2:$A$10,C2),"")

 

[batteredveg]

Much appreciated - thanks for your reply, guys!

 

[spoon_db]

Useful article:

Most Frequently Occurring Word in Excel - Easy Excel Tutorial

The solution to your problem is:

If your data column were named D, you could fetch the most frequently occurring in cell C1 using the formula

=INDEX(D,MODE(MATCH(D,D,0)))

Fetch the next most frequently occurring into cell C2 using the ARRAY formula

=INDEX(D,MODE(IF(COUNTIF(C$1:C1,D)=0,MATCH(D,D,0))))

Then fill C2 down into C3:C25.

This is working if you haven't a blank cell in "D" range... if you have a blank cell it crashes... I extend the formula with checking if the cell is empty - IF(D<>"";D)... but still needs some improvement ... The problem is that the formula returns " " - nothing, if the most frequently cell is blank, or the place on witch she is...

Also if you have equal counts, the formula will return the first match... but this is something witch you should know... this applies to all formulas...

Best regard,
Nikolay

 

[RatExcel]

Maybe try to covert your data into table (CTRL + T) or program your name range as a dynamic range using OFFSET function