title: VBA - dictionary running through a worksheet holding a key plus two related columns
body: <p>The idea here is that i am having a sheet with more than 80,000 rows and six different columns. what I care about is the 2 columns though. I want the vba to go through each row, get the source IP in column D and the last word in the message box in column F after this "->" symbol, an example of the word is "175.55.78.35:8545" because the numbers in between the dots vary i feel like the best way to separate the words is through " ". Then once it's done retrieving the values from all the rows, print the results in 3 columns on a new sheet.</p>
  
  <p>NOTE that one IP address can be related with many different words.</p>
  
  <p>What i am looking for, is the vba return the source IP in one column, the number of times that the IP was found in column D for one word to the next column, seperate it with a comma (if along with the comma it can go to the next line staying in the same cell, even better), then the number of times that it found a different a word etc. and lastly the next column have the word retrieved from column F which is related with the first count of the 2nd column, seperate that with a comma, then the second word being related again with the second count of the previous column and then go to the next row for the other IP and so forth.</p>
  
  <p><a href="https://i.stack.imgur.com/PDsz4.png" rel="nofollow noreferrer">this is how the table looks</a></p>
  
  <p>For the code this is what i have. If the comments are removed, the following code only counts the times the source ip is found in the worksheet and prints the results in a different sheet.</p>
  
  <pre><code>Sub timesofattack2()
  
  Dim data As Variant, key As Variant, dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
  With ThisWorkbook.Worksheets("Sheet1")
      data = .Range("D8", .Range("D" &amp; .Rows.count).End(xlUp)).Value
  End With
  
  For Each key In data
      dic(key) = dic(key) + 1
  Next
  
  With ThisWorkbook.Worksheets.Add
      .Range("A2").Resize(dic.count).Value = Application.Transpose(dic.Keys())
      .Range("B2").Resize(dic.count).Value = Application.Transpose(dic.Items())
  End With
  
  End Sub
  
  
   'Dim arrayofwords() As String 
  'arrayofwords = Split( , " ")
  'Dim word As String
  'Dim i As Integer
  
  'For i = LBound(arrayofwords) To UBound(arrayofwords)
   'If arrayofwords(i) = "-&gt;" Then
   'word = arrayofwords(i + 1)
  'End If
  </code></pre>