Thread: Dictionary Object

Adam

Here's a couple of ways, one using a collection, and the other using a dictionary.

Run each of the codes, and view the output. They both use a space as a word separator. The dictionary will see the and The as 2 different words, while the collection treats them the same.

 
Sub aaa()
  Set nodupes = New Collection
  For i = 1 To 12
    arr = Split(Cells(i, 1), " ")
    For j = LBound(arr) To UBound(arr)
      On Error Resume Next
      nodupes.Add Item:=arr(j), key:=arr(j)
      On Error GoTo 0
    Next j
  
  Next i
  Range("G:I").ClearContents
  For i = 1 To nodupes.Count
    Cells(i, "G") = nodupes(i)
  Next i
  
  Range("G:G").Sort key1:=Range("G1"), order1:=xlAscending, header:=xlNo
End Sub

 
Sub bbb()
  Set dic = CreateObject("scripting.dictionary")
  
  For i = 1 To 12
    arr = Split(Cells(i, 1), " ")
    For j = LBound(arr) To UBound(arr)
      If Not dic.exists(arr(j)) Then
        dic.Add Item:=arr(j), key:=arr(j)
      End If
    Next j
  
  Next i
  
  For Each ce In dic.items
    Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).Value = ce
  Next ce
  
  Range("I:I").Sort key1:=Range("I1"), order1:=xlAscending, header:=xlNo

End Sub

rylo

oops!!
i hope that rylo and AdamDay eccept my apology. Though i had read the rules long before but had forgotten that important one.

2). Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.

SHG
To avoid such an embaracing thing again, i will read the rules each time in future before i post.

soooooooooooooorry!! :-)

The attachment will histogram text taken from worksheet cells. I copied your post, for example, and it gave:

        ------A------ -B--
    1       Word      Freq
    2   the             21
    3   of              13
    4   to              13
    5   I               11
    6   a                9
    7   for              6
    8   need             6
    9   program          6
   10   words            6
   11   and              5
   12   be               5
   13   dictionary       5
   14   list             5
   15   also             4
   16   in               4
   17   is               4
   18   step             4
   19   will             4
   20   as               3
   21   from             3
   22   really           3
   23   your             3
   24   all              2
   25   am               2
   26   another          2
   27   by               2
   28   can              2
   29   count            2
   30   it               2
   31   just             2
   32   make             2
   33   most             2
   34   number           2
   35   project          2
   36   Thank            2
   37   that             2
   38   thing            2
   39   using            2
   40   which            2
   41   you              2
   42   after            1
   43   again            1
   44   alongside        1
   45   alter            1
   46   any              1
   47   appear           1
   48   appears          1
   49   attention        1
   50   back             1
   51   been             1
   52   best             1
   53   between          1
   54   bringing         1
   55   characters       1
   56   contribution     1
   57   contributions    1
   58   created          1
   59   de               1
   60   delimiter        1
   61   described        1
   62   different        1
   63   do               1
   64   duplication      1
   65   duplications     1
   66   entries          1
   67   excel            1
   68   Finally          1
   69   first            1
   70   frequency        1
   71   gain             1
   72   getting          1
   73   have             1
   74   help             1
   75   his              1
   76   hoping           1
   77   however          1
   78   if               1
   79   I'm              1
   80   important        1
   81   Infact           1
   82   into             1
   83   job              1
   84   like             1
   85   little           1
   86   maybe            1
   87   me               1
   88   more             1
   89   much             1
   90   must             1
   91   my               1
   92   not              1
   93   numbers          1
   94   object           1
   95   once             1
   96   orders           1
   97   perform          1
   98   poem             1
   99   possible         1
  100   process          1
  101   remove           1
  102   removed          1
  103   repeats          1
  104   result           1
  105   results          1
  106   rylo             1
  107   Secondly         1
  108   separately       1
  109   shg              1
  110   similar          1
  111   so               1
  112   some             1
  113   start            1
  114   still            1
  115   suggested        1
  116   superfluous      1
  117   take             1
  118   thanks           1
  119   then             1
  120   think            1
  121   third            1
  122   this             1
  123   time             1
  124   times            1
  125   transition       1
  126   two              1
  127   unsure           1
  128   usable           1
  129   use              1
  130   valuable         1
  131   view             1
  132   wali             1
  133   wali's           1
  134   ways             1
  135   What             1
  136   why              1
  137   with             1
  138   word             1
  139   write            1

Added a few new interfaces, so you can histogram the text on the clipboard, delete words, ...

Here's a little more to play with.

Try this version.

I found a bug in that version when the text from the clipboard is larger that 32K characters. This version fixes that; I tested it to about 250K. Also added a log.