Top 5 Text Strings from List - Formula advice

batteredveg

New Member
Joined
Aug 27, 2014
Messages
7
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:


[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Italy[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]United States of America[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ireland[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


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:


[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Top 5[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Germany[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]United Kingdom[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Italy[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]United States of America[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]France[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]


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

Much obliged,

Paul
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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
Code:
=COUNTIF($A$2:$A$10,A2)

in C2: ctrl-shift-enter, copy and drag down the array formula
Code:
=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
Code:
=IF(C2<>"",COUNTIF($A$2:$A$10,C2),"")
 
Upvote 0
Useful article:

Most Frequently Occurring Word in Excel - Easy Excel Tutorial

The solution to your problem is:

http://excelusergroup.org/forums/t/201.aspx said:
If your data column were named D, you could fetch the most frequently occurring in cell C1 using the formula
Code:
=INDEX(D,MODE(MATCH(D,D,0)))
Fetch the next most frequently occurring into cell C2 using the ARRAY formula
Code:
=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
 
Upvote 0
Maybe try to covert your data into table (CTRL + T) or program your name range as a dynamic range using OFFSET function ;)
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top