comparing two lists, finding the discrepancies

Posted by Keith Mueller on September 20, 2000 12:21 PM

I am trying something that should be simple. I have two lists of 9 digit ID numbers. I need to compare the lists and see which ones are not showing up on the other list.

Excel help files and microsoft's web site have been useless.

Any ideas?

Thanks,
Keith

Posted by Celia on September 21, 0100 1:15 AM

Keith
Or if you prefer a worksheet formula method, have a look at :-

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

which provides the following stucture for the formula :-
=IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")

Celia

Posted by David on September 20, 0100 11:05 PM

Excel help files and microsoft's web site have been useless. Any ideas?

Substitute a1 for the top of the list
and the workbook names and sheet names but it is a beginning. If you think it might work and can't figure it out give more detail.

Sub Macro1()

For x = 1 To Workbooks("workbook2.xls").Sheets(1).Range("A1").End(xlDown).Row
For y = 1 To Workbooks("workbook1.xls").Sheets(1).Range("A1").End(xlDown).Row
If Workbooks("workbook2.xls").Sheets(1).Cells(x, 1) = Workbooks("workbook1.xls").Sheets(1).Cells(y, 1) Then
found = True
y = Workbooks("workbook1.xls").Sheets(1).Range("A1").End(xlDown).Row + 1
End If
Next y
If found = False Then
Workbooks("workbook2.xls").Sheets(1).Cells(x, 1).Font.ColorIndex = 3
Workbooks("workbook2.xls").Sheets(1).Cells(x, 1).Font.Italic = True
End If
found = False

Next x

End Sub

Thanks,