Compare column in 2 documents

[Amiranda]

I have two documents: one with a sendlist, one with a directory of names and
addresses. In the sendlist I have about 1200 posts, and in the directory
about 900.
Now I need to check if the mailaddresses in the directory are also in the
sendlist.

It looks like this:
Directory:
Name Address Phone E-mail
Brita xxx 555 (e-mail address removed)
Carl yyy 777 (e-mail address removed)

Sendlist:
E-mail List 1 List 2
(e-mail address removed) x
(e-mail address removed) x x

I want this outcome:
Name Address Phone E-mail Added
Brita xxx 555 (e-mail address removed) YES
Carl yyy 777 (e-mail address removed)

So, I need to add a column in my directory where I can see a mark if that
post is also in the Sendlist.

I use Excel 2003 with SP3.

I tried to combine VLOOKUP and IF but just couldn't get it to work.

 

[Otto Moehrbach]

Amiranda
Place this macro in the Directory workbook. I assumed that the
Directory workbook is named "Directory.xls" and the Send List workbook is
named "Send List.xls". Change that in the code. I also assumed the sheet
in the Directory workbook is named "This" and the sheet in the Send List
workbook is named "That". Change those as needed. Come back if you need
more. HTH Otto
Sub CompareWBs()
Dim ShDir As Worksheet, ShSend As Worksheet
Dim ColADir As Range, ColASend As Range
Dim i As Range
Application.ScreenUpdating = False
Set ShDir = Workbooks("Directory.xls").Sheets("This")
Set ShSend = Workbooks("Send List.xls").Sheets("That")
With ShDir
Set ColADir = .Range("D2", .Range("D" & Rows.Count).End(xlUp))
End With
With ShSend
Set ColASend = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
For Each i In ColADir
If Not ColASend.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
i.Offset(, 1).Value = "YES"
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Amiranda]

works perfectly,
thank you so much!!

"Otto Moehrbach" skrev: