Top 5 Most Frequent Words In Multiple Columns and Rows

[Zoenaedy]

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?

 

[James Clear]

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