Comparing two columns of data
 

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.

Comparing two columns of data
 

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

"Wayne" wrote in message
...
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.

Comparing two columns of data
 

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

--
Gary''s Student - gsnu200825


"Wayne" wrote:

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.