Ignoring blank cells while locating common text value

[Lecum]

Dear all,

SOS please...

I am trying to get the most common WORD from my "sold Fish" list but since sometimes I have blank cells the formula keeps giving me n/a

Can anyone help me a provide me with a proper formula?



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]cod [/TD]
[TD]cod [/TD]
[TD]cod [/TD]
[TD]monkfish[/TD]
[TD] [/TD]
[TD]cod [/TD]
[TD]cod [/TD]
[TD]#n/a[/TD]
[/TR]
</tbody>[/TABLE]

 

[urobee]

Sub FindFrequency()
'Update 20140326
Dim Rng As Range
Dim WorkRng As Range
Set dic = CreateObject("scripting.dictionary")
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xMax = 0
xOutValue = ""
For Each Rng In WorkRng
    xValue = Rng.Value
    If xValue <> "" Then
        dic(xValue) = dic(xValue) + 1
        xCount = dic(xValue)
        If xCount > xMax Then
            xMax = xCount
            xOutValue = xValue
        End If
    End If
Next
MsgBox "The most common value is: " & xOutValue & " Appeared " & xMax & " Times"
End Sub

It's not my code, I found this on extendoffice.com !

You can modify the output to a cell.
Instead of using this:

MsgBox "The most common value is: " & xOutValue & " Appeared " & xMax & " Times"

try this for example:

Range("I1").Value = xOutValue

 

[Ron Coderre]

With your sample data in A1:G1
Here are two related approaches:
This one is an ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER, instead of just pressing ENTER:

H1: =INDEX(A1:G1,MATCH(MAX(COUNTIF(A1:G1,A1:G1&"")),COUNTIF(A1:G1,A1:G1&""),0))

This one is a regular formula (no C+S+E necessary):

H1: =INDEX(A1:G1,MATCH(MAX(INDEX(COUNTIF(A1:G1,A1:G1&""),0)),INDEX(COUNTIF(A1:G1,A1:G1&""),0),0))

Is that something you can work with?

 

[Lecum]

Dear Ron,

The formula it worked perfectly. I really thank you very much for your time and your help.

Thank you

David