compare column data

Posted by Gary on February 14, 2000 2:57 PM

I have 2 columns of part numbers. Some numbers are in both columns(in different rows)some are not. Is there any way of comparing the columns and reporting the differences. HELP

Posted by Celia on February 14, 2000 3:51 PM

Gary
You will find what you need at :-

http://www.cpearson.com/excel/duplicat.htm#InOneNotOther

Celia

Posted by Ivan Moala on February 14, 2000 7:40 PM

Hi gary
Try something like this;

Dim OrgRg
Dim ToCompRg
Dim x As Integer
Dim oCell
Dim cCell

Sub Compare1()

'set orginal range to compare
Range("A1").Select
Set OrgRg = Range(ActiveCell, ActiveCell.End(xlDown))
'Reset Colors
OrgRg.Interior.ColorIndex = xlNone
OrgRg.Font.ColorIndex = 0

'set other data to compare range
Range("B1").Activate
Set ToCompRg = Range(ActiveCell, ActiveCell.End(xlDown))

Application.ScreenUpdating = False

'Compare NOW
For Each oCell In OrgRg
For Each cCell In ToCompRg
If oCell = cCell Then
'MsgBox oCell.Text & ":" & cCell
With oCell.Interior
.ColorIndex = 15 'Grey
.Pattern = xlSolid
End With
oCell.Font.ColorIndex = 5 'Blue
End If
Next cCell
Next oCell
Application.ScreenUpdating = True
MsgBox "Completed comparison"

End Sub

This assumes your data is continuous in column
A & B and highlights the cells in A that are the same.

Ivan

Posted by Adam K. on February 22, 2000 12:20 PM

Thank you very much. This was very helpful