Comparing two lists and summarize differences

Belinda

Board Regular
Joined
Apr 5, 2004
Messages
61
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this, compares column A & B, puts results in C - F.

Code:
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
 
Upvote 0

Belinda

Board Regular
Joined
Apr 5, 2004
Messages
61
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.
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...

Rich (BB code):
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...
 
Upvote 0

Belinda

Board Regular
Joined
Apr 5, 2004
Messages
61
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
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Set ListA = Range("A2:A" & Cells(Rows.Count,"A").End(xlup).Row)
Set ListB = Range("B2:B" & Cells(Rows.Count,"B").End(xlup).Row)
 
Upvote 0

Forum statistics

Threads
1,190,794
Messages
5,982,956
Members
439,808
Latest member
agutosay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top