Most frequently occurring text in a range (combo of rows and columns)

[Soares79]

Hi,

I would like to see what the most frequently occurring text occurs in the whole range, not just line by line. How do i combine all? i.e. A1:F8? And ignore blank cells?

I see there are a multiple threads and posts on this topic already, but all focus in on 1 row or column at a time.

Any help is appreciated as always!

Thanks.

 

[barry houdini]

Re: Most frequently occurring text in a ange (combo of rows and columns)

Try this array formula

=INDIRECT(TEXT(MIN(IF(MAX(COUNTIF(A1:F8,A1:F8))=COUNTIF(A1:F8,A1:F8),ROW(A1:F8)*100+COLUMN(A1:F8))),"R0C00"),FALSE)

confirm with CTRL+SHIFT+ENTER

 

[Soares79]

Re: Most frequently occurring text in a ange (combo of rows and columns)

Hi, Thanks for the speedy response! Unfortunately the answer is coming up as "0" so I am guessing it is still picking up the blank cells?

I may be able to bypass this however as i actually need to top 5 occurrences, so that was going to be my next question, how do i find the second most frequent (and the number of times it appears), third most frequent (and the number of times it appears), and so on and so forth. If the above formula is picking up blanks, i will simply look for the second to sixth most common to find my top 5 so to speak.

 

[barry houdini]

Re: Most frequently occurring text in a ange (combo of rows and columns)

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

 

[Soares79]

Re: Most frequently occurring text in a ange (combo of rows and columns)

Wow! thank you so much! i can see column k (instances are picking up the number of times the text appear in order, but column J (text) shows the first two correct but then repeats the second most popular values over and over?

Id send through a screen shot but not sure how...

 

[barry houdini]

Re: Most frequently occurring text in a ange (combo of rows and columns)

but column J (text) shows the first two correct but then repeats the second most popular values over and over?

I can't recreate that - whenever I test it works as required. This part....

COUNTIF(J$1:J1,A$1:F$8)=0

stops that happening because it checks the values above so that they can't repeat. Are the values in A1:F8 text values? Did you change the formula at all - can you post the version you are using?

 

[Soares79]

Re: Most frequently occurring text in a ange (combo of rows and columns)

I am searching for text. Not numbers. I can see the number of times they appear, but not the name that appears the most.

Formula I am using is {=INDIRECT(TEXT(MIN(IF('3MM REGISTER'!N$9:AN$265<>"",IF(COUNTIF('3MM REGISTER'!N$9:AN$265,'3MM REGISTER'!N$9:AN$265)=O7,IF(COUNTIF(N$6:N6,'3MM REGISTER'!N$9:AN$265)=0,ROW('3MM REGISTER'!N$9:AN$265)*1000+COLUMN('3MM REGISTER'!N$9:AN$265))))),"R0C000"),FALSE)}

 

[barry houdini]

Re: Most frequently occurring text in a ange (combo of rows and columns)

Formula I am using is {=INDIRECT(TEXT(MIN(IF('3MM REGISTER'!N$9:AN$265<>"",IF(COUNTIF('3MM REGISTER'!N$9:AN$265,'3MM REGISTER'!N$9:AN$265)=O7,IF(COUNTIF(N$6:N6,'3MM REGISTER'!N$9:AN$265)=0,ROW('3MM REGISTER'!N$9:AN$265)*1000+COLUMN('3MM REGISTER'!N$9:AN$265))))),"R0C000"),FALSE)}

OK, I see the problem, where you are referring to data on another sheet the sheet name also needs to be included in the INDIRECT function, so try like this:

=INDIRECT("'3MM REGISTER'!"&TEXT(MIN(IF('3MM REGISTER'!N$9:AN$265<>"",IF(COUNTIF('3MM REGISTER'!N$9:AN$265,'3MM REGISTER'!N$9:AN$265)=O7,IF(COUNTIF(N$6:N6,'3MM REGISTER'!N$9:AN$265)=0,ROW('3MM REGISTER'!N$9:AN$265)*1000+COLUMN('3MM REGISTER'!N$9:AN$265))))),"R0C000"),FALSE)

 

[Soares79]

Re: Most frequently occurring text in a ange (combo of rows and columns)

Fanastic!!! Thanks so much. Works a treat.