Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) | |||
linking values of column A to values of column B | Excel Discussion (Misc queries) | |||
how do i sum up a column of time values but ignore negative values | New Users to Excel | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions |