most often values of a column

T

Tamas Konczer

I would like to count and determine the most often (10) occurent
values (strings) of a column.

How can I do that?
Thank you very much your help.
 
B

bj

if your data is in A in a helper column in row 1
enter
=if(counta(A$1:A1>1,0,Counta(A:A,A1))
copy to the end of your data
then use auto filter on your helper column to select the top 10
 
J

Joel

The only way to do this is with a custtom function. i wrote one below. You
enter a range of cells and the occurance you want to return. the function
returns the string with the occurance specified. Use =countif() if you need
to know the number of times it occurs.

call with
=Occurances(M7:M17,3) with any range of numbers and any occuarance as the
2nd parameter. It is not limited to 10. It will work with any size range
and any size frequency


Function Occurances(MyCells As Range, Count As Integer) As String

'Dim Results(1)

ReDim Results(MyCells.Count, 2)

Resultsize = 0

For Each Cell In MyCells

Found = False
For i = 1 To Resultsize

If StrComp(Cell.Value, Results(i, 1)) = 0 Then

Found = True
Results(i, 2) = Results(i, 2) + 1
Exit For
End If

Next

If Found = False Then

Resultsize = Resultsize + 1
Results(Resultsize, 1) = Cell.Value
Results(Resultsize, 2) = 1
End If


Next Cell

If Count > Resultsize Then
Occurances = ""
Else

'sort results
For i = 1 To (Resultsize - 1)
For j = (i + 1) To Resultsize

If (Results(j, 2) > Results(i, 2)) Then

temp = Results(j, 1)
Results(j, 1) = Results(i, 1)
Results(i, 1) = temp

temp = Results(j, 2)
Results(j, 2) = Results(i, 2)
Results(i, 2) = temp

End If


Next j
Next i

End If
 
J

Joel

If you actually need the string returned, then my previous response is the
only solution that is posted that will give the string. the others are only
giving the count.
 
H

Harlan Grove

Joel said:
The only way to do this is with a custtom function. . . .
Click to expand...
....

Wrong. With data in a range named D and the instance number in a cell
named N, the array formula

=MATCH(LARGE(IF(MATCH(D,D,0)=ROW(D)-MIN(ROW(D))+1,
COUNTIF(D,D)+1-ROW(D)/ROWS(D)),N),IF(MATCH(D,D,0)
=ROW(D)-MIN(ROW(D))+1,COUNTIF(D,D)+1-ROW(D)/ROWS(D)),0)

And if you're going to use a udf, at least make it as flexible as
possible, e.g., it should process ranges and arrays, and since ranges
can have more than 32767 cells, the instance number argument should be
at least type Long rather than Integer (and it'd need to be type
Variant to handle arbitrary ranges in XL2007).

Here's an alternative way to do this in VBA.


Function foo(a As Variant, n As Long) As Variant
Dim x As Variant, c As New Collection
Dim v() As Variant, i As Long, j As Long, k As Long

foo = CVErr(xlErrNum) 'EXPECTED error value if n out of range
k = Application.WorksheetFunction.CountA(a)

'fail quickly on impossible n
If n < 1 Or k < n Then Exit Function

ReDim v(1 To 3, 1 To k)

On Error Resume Next

For Each x In a
i = i + 1

'following errs on 1st instance of each value in a
'otherwise sets j to appropriate 2nd index for v
j = c.Item(x)

If Err.Number <> 0 Then '1st instance
Err.Clear
j = c.Count + 1
c.Add Item:=j, key:=CStr(x)
v(1, j) = i
v(2, j) = CDbl(k - j)
v(3, j) = CStr(x)

Else 'increment instance count
v(2, j) = v(2, j) + CDbl(k)

End If

Next x

On Error GoTo 0

'fail quickly if there are fewer than n distinct values in a
If n > c.Count Then Exit Function

'returns a 3 item array: (serialized for 2+D) index within a of
'1st instance of nth most frequently occurring value, its index
'within DISTINCT values in a, and the value itself
With Application.WorksheetFunction
x = .Index(v, 2, 0)
j = .Match(.Large(x, n), x, 0)
v(2, j) = c.Item(v(3, j))

foo = .Transpose(.Index(v, 0, j)) 'transpose optional

End With

End Function
 
Top