Top 5 Most Frequent Words In Multiple Columns and Rows

Zoenaedy

New Member
Joined
Jul 9, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Hi, I use this formula to get the top 5 most frequent words appear in a range with multiple rows and columns A1:F8:


For top 5 try this:

In K1 text value "instances" and then in K2 this array formula

=LARGE(IF(A$1:F$8<>"",COUNTIF(A$1:F$8,A$1:F$8)),SUM(K$1:K1)+1)

in J1 text value "text" and this array formula in J2

=INDIRECT(TEXT(MIN(IF(A$1:F$8<>"",IF(COUNTIF(A$1:F$8,A$1:F$8)=K2,IF(COUNTIF(J$1:J1,A$1:F$8)=0,ROW(A$1:F$8)*1000+COLUMN(A$1:F$8))))),"R0C000"),FALSE)

confirm both formulas with CTRL+SHIFT+ENTER and copy down as far as required

blanks will be ignored

It works, but when there are 2 words with the same frequency, it will not display the other. For example: "Love" occurs 4x, "Joy" occurs 4x and "Peace" occurs 3x, this formula will reveal Love as the topmost, then Peace as the second most frequent. How to get the actual order: 1st Love, 2nd Joy, 3rd Peace?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi, I use this formula to get the top 5 most frequent words appear in a range with multiple rows and columns A1:F8:


For top 5 try this:

In K1 text value "instances" and then in K2 this array formula

=LARGE(IF(A$1:F$8<>"",COUNTIF(A$1:F$8,A$1:F$8)),SUM(K$1:K1)+1)

in J1 text value "text" and this array formula in J2

=INDIRECT(TEXT(MIN(IF(A$1:F$8<>"",IF(COUNTIF(A$1:F$8,A$1:F$8)=K2,IF(COUNTIF(J$1:J1,A$1:F$8)=0,ROW(A$1:F$8)*1000+COLUMN(A$1:F$8))))),"R0C000"),FALSE)

confirm both formulas with CTRL+SHIFT+ENTER and copy down as far as required

blanks will be ignored

It works, but when there are 2 words with the same frequency, it will not display the other. For example: "Love" occurs 4x, "Joy" occurs 4x and "Peace" occurs 3x, this formula will reveal Love as the topmost, then Peace as the second most frequent. How to get the actual order: 1st Love, 2nd Joy, 3rd Peace?
Can you please provide your data in the excel format by using XL2BB
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

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