VBA- Filtering duplicated array

[Paffs]

Hello coders! I am a teenager at my first internship and I've been tasked with producing an excel file that with the push of a button turns a whole bunch of text into a wordlist of the most frequent words, in array format. So far I've successfully managed to scan separate words into an array that I call WordArray, now I want to count the occurencies of the duplicates in this array, and I've settled for the dictionary function built into VBA. So, with that done I have a dictionary with a bunch of words and frequencies assigned to them. I now want the data from this dictionary to be entered into another array with the name FinWordArray without their numbers assigned to them. I also want this action to be filtered so that any words with a frequency less than 10 will not be moved into FinWordArray. I should mention that outputting the data into plots and then running a =IF(COUNTIF(A:A)greater than 9)

Thank you in advance for any help!
I am using Office 2010 on a windoes 7 64bit system.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

The code depends on how you have stored the information in the dictionary.

Assuming that you have stored the Words in the Dictionary Keys, and the Counts in the Key's Items, this example shows one way to transfer the values with counts of 10 or more to an array.

Sub ConditionalDictionaryToArray()
    Dim c As Range
    Dim iIndex As Long, i As Long
    Dim vKey As Variant
    Dim dict As Scripting.Dictionary
    Dim sFreqWords() As String
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    '--populate dictionary:
    '    Unique values stored as Keys
    '    Counts of each value stored as Items
    For Each c In Range("A1:A100")
        If Not dict.Exists(CStr(c.Value)) Then
            dict.Add CStr(c.Value), 1
        Else
            dict(c.Value) = dict(c.Value) + 1
        End If
    
    Next c
    
    '--size dynamic array to max size needed
    ReDim sFreqWords(1 To dict.Count)
    
    For Each vKey In dict.Keys
        If CLng(dict(vKey)) > 9 Then
            iIndex = iIndex + 1
            sFreqWords(iIndex) = vKey
        End If
    Next vKey
    
    '--Resize array
        ReDim Preserve sFreqWords(1 To iIndex)
    '--display results

    For i = LBound(sFreqWords) To UBound(sFreqWords)
        Debug.Print sFreqWords(i)
    Next i

End Sub

 

[Paffs]

Thank you, that really helped me! With a few tweaks I managed to fit it to my situation entirely, I had to change up the way c works in the latter half though. I changed it to variant in order to be able to search an array, and then had to change the way dict interacts with c because c was no longer an object. But now the program does what I intended it to do, and I can get on with the rest of the program. So thank you, kind sir for the help

 Dim dict As Object, iIndex As Long, vKey As Variant, c As Variant, a As Long
 Dim i As Long, v As Variant, y As Long, x As Long, z As Long
 Set dict = CreateObject("Scripting.Dictionary")
 
    For Each c In WordArray
        If Not dict.Exists(CStr(c)) Then
            dict.Add CStr(c), 1
        Else
            dict(c) = dict(c) + 1
        End If
    
    Next c
    
    '--size dynamic array to max size needed
    ReDim FinWordArray(1 To dict.Count)
    
    For Each vKey In dict.Keys
        If CLng(dict(vKey)) > 9 Then
            iIndex = iIndex + 1
            FinWordArray(iIndex) = vKey
        End If
    Next vKey

 

[Jerry Sullivan]

You're welcome and good luck with your internship!