Compare two email lists and output non-matching

Hi,

I am a newbie and need your expert help please.

I have two email lists. For example they are:

List 1:

a@spam.com
b@spam.com
c@spam.com

List 2:

abcdef@spam.com
b@spam.com
c@spam.com

In list 1 the non-matching address is a@spam.com. namely it is not common to both lists. I want to automatically go through the lists and output firstly a new list of the non-matches from column 1 and also a new list of the non-common items from list two.

So in this case the result would be that somewhere there is a new list from list 1 that has "a@spam.com" and secondly there is a new list from list 2 that has "abcdef@spam.com."

Obvioulsy the real list is more complicated and longer but the principle will be the same.

Can anyone help please?

In a column near List1 use a formula like this:

=Isnumber(Match(A1,B:B,0)) where A1 is the first address to search for and B:B is the entire column B that List 2 is located in. Then copy the formula down.

You will get TRUE if matches are found adn FALSE if none are found.

You can then filter this list through DATA|FILTER|AUTOFILTER, filtering for FALSE and copy/paste it elsewhere.

You can repeat the process for comparing List2 to List1 , just changing the references in the formula above to suit.

See attached for example of what I meant...

Hello Axel_Max,

Here is another version that is fully automated. Click the button and your done. The macro makes use of the Dictionary object to do the comparisons and remove any duplicates. Here is the macro for those who are interested.

Sub ListUniques()

  Dim I As Long
  Dim Keys1 As Variant
  Dim Keys2 As Variant
  Dim LastRow As Long
  Dim List1 As Object
  Dim List2 As Object
  Dim Rng1 As Range
  Dim Rng2 As Range
  Dim Unique1 As Range
  Dim Unique2 As Range
  
    Set List1 = CreateObject("Scripting.Dictionary")
    Set List2 = CreateObject("Scripting.Dictionary")
    
      List1.CompareMode = 1
      List2.CompareMode = 1
  
      With Worksheets("Sheet1")
       'Define the ranges for List1, List2, Unique1, and Unique2
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
          Set Rng1 = IIf(LastRow = 1, .Range("A2"), .Range("A2", .Cells(LastRow, "A")))
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
          Set Rng2 = IIf(LastRow = 1, .Range("B2"), .Range("B2", .Cells(LastRow, "B")))
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
          Set Unique1 = IIf(LastRow = 1, .Range("C2"), .Range("C2", .Cells(LastRow, "C")))
        LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
          Set Unique2 = IIf(LastRow = 1, .Range("D2"), .Range("D2", .Cells(LastRow, "D")))
      End With
      
       'Create an array of unique values in List1
        For Each Cell In Rng1
          If Not List1.Exists(Cell) Then
            List1.Add Cell.Text, 1
          End If
        Next Cell
        
       'Create an array of unique values in List2
        For Each Cell In Rng2
          If Not List2.Exists(Cell) Then
            List2.Add Cell.Text, 2
          End If
        Next Cell

       'Load the comparison arrays
        Keys1 = List1.Keys
        Keys2 = List2.Keys
          
         'Put the unique values of List2 in column "C"
          I = 1
          Unique1.ClearContents
          For Each K In Keys1
            If Not List2.Exists(K) Then
              Unique1.Item(I) = K
              I = I + 1
            End If
          Next K
          
         'Put the unique values of List1 in column "D"
          I = 1
          Unique2.ClearContents
          For Each K In Keys2
            If Not List1.Exists(K) Then
              Unique2.Item(I) = K
              I = I + 1
            End If
          Next K

   'Free memory from objects
    Set List1 = Nothing
    Set List2 = Nothing
          
End Sub

Sincerely,
Leith Ross