Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frequncy of unique values in array
Suppose this :
Sub Test() Dim str1 As String Dim str2 As String Dim i As Integer Dim arrTokens() As String str1 = "tftftffftftftftftftf" str2 = Left(str1, 1) For i = 2 To Len(str1) If Mid(str1, i, 1) = Mid(str1, i - 1, 1) Then str2 = str2 & Mid(str1, i, 1) Else str2 = str2 & "," & Mid(str1, i, 1) End If Next i ReDim arrTokens(dhCountTokens(str2, ",") - 1) arrTokens = Split(str2, ",") End Sub I need to know the frequncy of each unique value of arrTokens array. In this case: T = 9 F = 8 FFF = 1 Any ideas? Thanks in advance. Fabio Coatis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frequncy of unique values in array
Hi Fábio
Here's one way: A collection cannot have duplicate keys, so On Error Resume Next sees to, that the routine continues, each time this situation occurs. After the loop CheckColl contains one instance of each element in str2. Sub Test() Dim Counter As Long Dim str1 As String Dim str2 As String Dim i As Integer Dim arrTokens() As String Dim CheckColl As New Collection Dim Element As Variant str1 = "tftftffftftftftftftf" str2 = Left(str1, 1) For i = 2 To Len(str1) If Mid(str1, i, 1) = Mid(str1, i - 1, 1) Then str2 = str2 & Mid(str1, i, 1) Else str2 = str2 & "," & Mid(str1, i, 1) End If Next i ReDim arrTokens(dhcounttokens(str2, ",") - 1) arrTokens = Split(str2, ",") On Error Resume Next For Counter = LBound(arrTokens) To UBound(arrTokens) - 1 CheckColl.Add Item:=arrTokens(Counter), key:=arrTokens(Counter) Next Counter str2 = "," & str2 & "," For Each Element In CheckColl MsgBox "Frequency of " & Element & "= " & _ dhcounttokens(str2, "," & Element & ",") / (Len(Element) + 2) Next Element End Sub -- Best Regards Leo Heuser MVP Excel Followup to newsgroup only please. "Fábio Coatis" skrev i en meddelelse ... Suppose this : Sub Test() Dim str1 As String Dim str2 As String Dim i As Integer Dim arrTokens() As String str1 = "tftftffftftftftftftf" str2 = Left(str1, 1) For i = 2 To Len(str1) If Mid(str1, i, 1) = Mid(str1, i - 1, 1) Then str2 = str2 & Mid(str1, i, 1) Else str2 = str2 & "," & Mid(str1, i, 1) End If Next i ReDim arrTokens(dhCountTokens(str2, ",") - 1) arrTokens = Split(str2, ",") End Sub I need to know the frequncy of each unique value of arrTokens array. In this case: T = 9 F = 8 FFF = 1 Any ideas? Thanks in advance. Fabio Coatis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frequncy of unique values in array
Thank you Leo and harlan,
Your insights were very helpful. My best regards and wishes for your success. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frequncy of unique values in array
You're welcome, Fábio.
"Fábio Coatis" skrev i en meddelelse ... Thank you Leo and harlan, Your insights were very helpful. My best regards and wishes for your success. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting unique text values in an array of data | Excel Worksheet Functions | |||
Unique values in an array. | Excel Discussion (Misc queries) | |||
Returning an array of unique values? | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions |