2 lists of values - identify differences?

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

Hey everyone,

I've developed a delightful little macro but have just one littl
problem with it.

The output is a checklist which contains to lists of values in column
and B respectively. The two lists should contain the same value
however small differences can sometimes exist. I would like a macro t
recognise where these differences occur - is this possible.

An example of what imean -

List A List B
(Col A) (Col B)

1 1
2 2
3 4
4 5
6 6

Can i get a macro to report that value 5 is missing from list A but i
list B, and that value 3 is missing from list B but in list A?

Can the result be reported in column D?

Many many thanks for any help :-
 
Sub CompColumns()
Dim rngA As Range, RngB As Range, Cell As Range
Dim rw As Long
Set rngA = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
Set RngB = Range(Cells(1, 2), Cells(1, 2).End(xlDown))
Cells(1, 4).Value = "In A, not B"
rw = 2
For Each Cell In rngA
If Application.CountIf(RngB, Cell.Value) = 0 Then
Cells(rw, 4).Value = Cell.Value
rw = rw + 1
End If
Next
Cells(rw, 4).Value = "In B, not A"
rw = rw + 1
For Each Cell In RngB
If Application.CountIf(rngA, Cell.Value) = 0 Then
Cells(rw, 4).Value = Cell.Value
rw = rw + 1
End If
Next
End Sub
 
Back
Top