Comparing two columns of data

[Wayne]

I am trying to compare two colomuns of data and produce a report showing the
differences. So far non of the formulas will work for what I need and I am
attempting to do some VBA coding to make looping test that can preform this.
Does any one have any suggestions as to how best accomplish my task.

 

[Fred Smith]

Try this (assuming your columns are A and B):
=countif(a:a,b1)

If there are no duplicates, the result will be zero. You can filter on the
non-zeros to show only the duplicates.

Regards,
Fred

 

[Gary''s Student]

Say we have data in A1 thru B20:

73 15
92 29
72 39
75 37
61 32
6 84
63 54
47 80
1 67
90 21
49 93
44 61
21 18
18 94
43 56
67 76
76 19
97 45
87 51
71 33

The following macro loops thru both columns looking for non-matches:

Sub ListMisMatches()
Set ra = Range("A1:A20")
Set rb = Range("B1:B20")
k = 1
For i = 1 To 20
v = Cells(i, 1).Value
If Application.WorksheetFunction.CountIf(rb, v) = 0 Then
Cells(k, "C").Value = "cell A" & i & " contains " & v & " not in
column B"
k = k + 1
End If
Next

For i = 1 To 20
v = Cells(i, 2).Value
If Application.WorksheetFunction.CountIf(ra, v) = 0 Then
Cells(k, "C").Value = "cell B" & i & " contains " & v & " not in
column A"
k = k + 1
End If
Next

End Sub

and the result in column C:

cell A1 contains 73 not in column B
cell A2 contains 92 not in column B
cell A3 contains 72 not in column B
cell A4 contains 75 not in column B
cell A6 contains 6 not in column B
cell A7 contains 63 not in column B
cell A8 contains 47 not in column B
cell A9 contains 1 not in column B
cell A10 contains 90 not in column B
cell A11 contains 49 not in column B
cell A12 contains 44 not in column B
cell A15 contains 43 not in column B
cell A18 contains 97 not in column B
cell A19 contains 87 not in column B
cell A20 contains 71 not in column B
cell B1 contains 15 not in column A
cell B2 contains 29 not in column A
cell B3 contains 39 not in column A
cell B4 contains 37 not in column A
cell B5 contains 32 not in column A
cell B6 contains 84 not in column A
cell B7 contains 54 not in column A
cell B8 contains 80 not in column A
cell B11 contains 93 not in column A
cell B14 contains 94 not in column A
cell B15 contains 56 not in column A
cell B17 contains 19 not in column A
cell B18 contains 45 not in column A
cell B19 contains 51 not in column A
cell B20 contains 33 not in column A