Comparing two lists and summarize differences

[Belinda]

How do I use VBA to compare two lists (List A & List B) and get a summary report on the same worksheet which: 1) Gives a count number of items for each list; 2) Lists which items are in List A but not in List B; 3) Lists which items are in List B but not in List A. Thank you.

 

[Jonmo1]

Try this, compares column A & B, puts results in C - F.

Sub Test()
Dim ListA As Range
Dim ListB As Range
Dim c As Range
Set ListA = Range("A1:A100")
Set ListB = Range("B1:B100")
Range("C1").Value = "Values in A that are NOT in B"
Range("D1").Value = "Values in B that are Not in A"
Range("E1").Value = "Count of A"
Range("F1").Value = "Count of B"
For Each c In ListA
If c.Value <> "" Then
    Range("E2").Value = Range("E2").Value + 1
    If Application.CountIf(ListB, c) = 0 Then
        Cells(Cells(Rows.count, "C").End(xlUp).Row + 1, "C").Value = c
    End If
End If
Next c
For Each c In ListB
If c.Value <> "" Then
    Range("F2").Value = Range("F2").Value + 1
    If Application.CountIf(ListA, c) = 0 Then
        Cells(Cells(Rows.count, "D").End(xlUp).Row + 1, "D").Value = c
    End If
End If
Next c
End Sub

 

[Belinda]

Thanks jonmo1. It worked great!. How can the code be modified to make the following adjustments:
1) Rather than give ListA and ListB a definite range, make the code more flexible so that it can be used it for different list sizes.

2) Highlight the elements in ListA (In Column1) that are not in ListB with a yellow color and the elements in ListB (In Column2) that are not in A with a blue color. Thank you.

 

[Jonmo1]

OK, changed it to do that. You'll have to do a trial and error with the Index #s for the colors, and it puts the count at the bottom of each column, instead of in another cell to the right...

Sub Test()
Dim ListA As Range
Dim ListB As Range
Dim c As Range
Set ListA = Range("A1:A100")
Set ListB = Range("B1:B100")
For Each c In ListA
If c.Value <> "" Then
    If Application.CountIf(ListB, c) = 0 Then
        c.Interior.ColorIndex = 6
    End If
End If
Next c
With Cells(Cells(Rows.count, ListA.Column).End(xlUp).Row + 1, ListA.Column)
    .Value = Application.CountA(ListA)
    .Font.Bold = True
End With
For Each c In ListB
If c.Value <> "" Then
    If Application.CountIf(ListA, c) = 0 Then
        c.Interior.ColorIndex = 5
    End If
End If
Next c
With Cells(Cells(Rows.count, ListB.Column).End(xlUp).Row + 1, ListB.Column)
    .Value = Application.CountA(ListB)
    .Font.Bold = True
End With
End Sub

the bolded lines you have to adjust to get the right color index #s to suit...

 

[Belinda]

Thanks again, jonmo1. Your code worked great. I am trying to modify the code so that the the range of each column is automatically selected by using "End(xldown)". I am missing something because it is not working. Your help is much appreciated.

Sub Compare2Lists()

Dim ListA As Range
Dim ListB As Range
Dim c As Range

Set ListA = Range("A2").End(xlDown)
Set ListB = Range("B2").End(xlDown)

Range("D4").Value = "In List A but NOT in List B"
Range("E4").Value = "In List B_but NOT in List A"
Range("D1").Value = "Count of A"
Range("E1").Value = "Count of B"

For Each c In ListA
If c.Value <> "" Then
Range("D2").Value = Range("D2").Value + 1
If Application.CountIf(ListB, c) = 0 Then
c.Interior.ColorIndex = 22
Cells(Cells(Rows.Count, "D").End(xlUp).Row + 1, "D").Value = c
End If
End If
Next c

For Each c In ListB
If c.Value <> "" Then
Range("E2").Value = Range("E2").Value + 1
If Application.CountIf(ListA, c) = 0 Then
c.Interior.ColorIndex = 8
Cells(Cells(Rows.Count, "E").End(xlUp).Row + 1, "E").Value = c
End If

End If
Next c

End Sub

 

[Jonmo1]

Set ListA = Range("A2:A" & Cells(Rows.Count,"A").End(xlup).Row)
Set ListB = Range("B2:B" & Cells(Rows.Count,"B").End(xlup).Row)

 

[Belinda]

Thank you, jonmo1. Macro works perfect.

 

[Edwardryez]

Thank you, jonmo1. Macro works perfect.

Great Macro, How can be added one more line to show the items that are the same in both lists?