Comparing cell data (2003)

[Badger1]

Hi,

Im trying to compare two columns of data, A & B. If there is some data in the cells in column B that isnt in the cells in A then I need to list this in column C ! Im quite new to the macro world and therefore any suggestions would be appreciated.

Cheers

[HansV]

You don't need a macro for this, you can use a formula. Say that the data in column A are in A1:A100. The formula
=MATCH(B1,$A$1:$A$100,0)
in C1 will return a number if the value in B1 can be found in A1:A100, and the error value #N/A (not available) otherwise. You can modify the way the result is displayed, for example:
=IF(ISNA(MATCH(B1,$A$1:$A$100,0)),"X","")
will display an X is the value is not found, and leave the cell blank otherwise. You can fill down the formula as far as needed. See attached example.

[Badger1]

Hi,

Thanks for the help, but ideally, with your example, "Dewitt" should be listed in column C as this name is in column B but not A. Is it possible for this to be listed in C?

Hope this doesnt sound too confusing.

Regards
Badger1

[HansV]

Do you want to list the names in C next to the same name in B, or should they all be at the top, starting in C1?

[Badger1]

The data in column C can start at the top, the list Im comparing is very long so it could well be the case that column C will only contain a few entries.

Cheers.
Badger1

[HansV]

Here is a macro solution. The code loops through the populated cells in column B and each time it finds an unmatched name, adds it to column C.

Warning: column C is cleared at the beginning of the macro.

Sub FindNonMatching()
Dim lngRow As Long
Dim lngMaxRow As Long
Dim lngCurRow As Long
Range("C:C").Clear
lngMaxRow = Range("B65536").End(xlUp).Row
For lngRow = 1 To lngMaxRow
If VarType(Application.Match(Range("B" & lngRow), _
Range("A:A"), 0)) = vbError Then
lngCurRow = lngCurRow + 1
Range("C" & lngCurRow) = Range("B" & lngRow)
End If
Next lngRow
End Sub

[Badger1]

That works great.

Thanks very much HansV for your time, its much appreciated.

Best Regards
Badger1