most often values of a column
 

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.

most often values of a column
 

if your data is in A in a helper column in row 1
enter
=if(counta(A$1:A11,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
"Tamas Konczer" wrote:

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.

most often values of a column
 

Try this array formula (ctrl+shift+enter to execute):

=MAX(COUNTIF(A1:A11,A1:A11))


On 14 Mar, 12:18, "Tamas Konczer" wrote:
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.

most often values of a column
 

Perhaps (?) ...

=IF(COUNTIF(A$1:A1,A1)1,0,COUNTIF(A:A,A1))

"bj" wrote:

if your data is in A in a helper column in row 1
enter
=if(counta(A$1:A11,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
"Tamas Konczer" wrote:

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.

most often values of a column
 

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

"Tamas Konczer" wrote:

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.

most often values of a column
 

Thanks, good catch
I should have put
=if(counta(A$1:A1,"1,0"),Counta(A:A,A1))
"Toppers" wrote:

Perhaps (?) ...

=IF(COUNTIF(A$1:A1,A1)1,0,COUNTIF(A:A,A1))

"bj" wrote:

if your data is in A in a helper column in row 1
enter
=if(counta(A$1:A11,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
"Tamas Konczer" wrote:

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.

most often values of a column
 

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.

"Tamas Konczer" wrote:

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.

most often values of a column
 

I did it with formula but I recognized that really better with a macro.

"Joel" wrote in message ...
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.

"Tamas Konczer" wrote:

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.

most often values of a column
 

Joel wrote...
The only way to do this is with a custtom function. . . .
....

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