Top 5 Text Strings from List - Formula advice

batteredveg

New Member
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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

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),"")``

Much appreciated - thanks for your reply, guys!

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

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

Replies
1
Views
249
Replies
4
Views
204
Replies
14
Views
459
Replies
2
Views
403
Replies
10
Views
397

1,206,814
Messages
6,075,008
Members
446,114
Latest member

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?

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

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