title: Match cell values and compensate for duplicates
body: <p>The headline isn´t very good so here is the explanation.</p>
  
  <p>I need to match cell values in column A to column B to find what cell values that are missing from column B.
  The problem is that there might be duplicate values. I.e column A has two "Orange" and column B has one "Orange". In this case one "Orange" is missing.</p>
  
  <p>I will write the missing values in, let´s say, column C.</p>
  
  <p>My approach has been to try to create a collection of the valuse form Column A. Run Column B's values against the collection and delete is there is a match. Then write the remaining values in to Column C.</p>
  
  <p>However as you probaly know collection can't handle duplicate values.</p>
  
  <p>I considered using array but deleteing cells from an array seem to be no simple matter form what I have seen.</p>
  
  <p>My limitations is that I can not make any changes to the data I have in the excel file. I.e delete data or add a color to the matched cell etc, this means I can't mark the alreay matched once in a convinient way.</p>
  
  <p>I have no experience with dictionary or if it has any solution to this, but I am not sure that it´s a doable approach since it requires checking a reference.
  I don't think copying the data over to a second excel sheet is the right approach either since that might mess upp other stuff going on on the computer.</p>
  
  <p>The question is simply, what alternatives are there? If non I´ll have to make a work-around with the tools I already have. But if there is a way which I have yet not found...</p>
  
  <p>This is the collection approach I wrote.</p>
  
  <pre><code>    Sub Test()
  
      Dim wb As Workbook
      Dim ws As Worksheet
      Dim cell As Range
      Dim rng As Range
  
      Dim colec As Collection
  
      Set colec = New Collection
  
      Set wb = ActiveWorkbook
      Set ws = wb.ActiveSheet
  
      Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(5, 1))
  
       For Each cell In rng.Cells
  
          If ExistsInCollection(colec, CStr(cell.Value)) = False Then
  
              On Error Resume Next
              colec.Add cell.Value, CStr(cell.Value) 'Adds the first selected range to collection
              On Error GoTo 0
  
          Else
  
              colec.Add cell.Value
  
          End If
  
      Next cell
  
      Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))
  
      For Each cell In rng.Cells
  
              On Error Resume Next
              colec.Remove (CStr(cell.Value))
              On Error GoTo 0
  
      Next cell
  End Sub
  </code></pre>
  
  <p>This is the function I copied which checks if a value already exists in the collection.</p>
  
  <pre><code>'Copied from outside source
  Private Function ExistsInCollection(pColl, ByVal pKey As String) As Boolean
      On Error GoTo NoSuchKey
      If VarType(pColl.Item(pKey)) = vbObject Then
           ' force an error condition if key does not exist
      End If
      ExistsInCollection = True
      Exit Function
  
  NoSuchKey:
      ExistsInCollection = False
  End Function
  </code></pre>
  
  <p>Please tell me if I have to clarify anything.</p>
  
  <p>I am thankfull for any help you can provide!</p>
  
  <p>/Henrik</p>