Need help comparing & flagging email lists in separate worksheets

[theirieone]

Problem:

column1 of worksheet1 contains a list of bad email addresses


column1 of worksheet2 contains a list of all email addresses on file


i need to compare the list of bad addresses (worksheet1) to the list of

all email addresses (worksheet2) and if there is an email address in
the bad list (worksheet1) which is also in the all emails
list(worksheet2), i need to flag it by putting "bad" in the next column

of the all addresses list (worksheet2).


i've been looking through this forum for the past few days and can't
seem to find something that can do this. it looks like it should be
simple but i've had no luck. any help would be appreciated.


p.s. the all email address list (worksheet2) can be quite long...around

8000 rows.


Rich

 

[merjet]

Sub MarkBad()
Dim iRow As Long
Dim c1 As Range
Dim c2 As Range
Dim rng1 As Range
Dim rng2 As Range

iRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row
Set rng1 = Worksheets("Sheet1").Range("A1:A" & iRow)
iRow = Worksheets("Sheet2").Range("A1").End(xlDown).Row
Set rng2 = Worksheets("Sheet2").Range("A1:A" & iRow)
For Each c1 In rng1
For Each c2 In rng2
If c1 = c2 Then c2.Offset(0, 1) = "bad"
Next c2
Next c1
End Sub

HTH,
Merjet

 

[Doug Glancy]

Rich,

You can do this with a formula - no VBA required. Assuming your "all email"
list starts in cell A2 of sheet "All" and the bad email list are in cell A2
to A2000 of sheet "Bad", paste this formula in cell B2 of sheet "All" and
copy down to the last email:

=IF(COUNTIF(bad!$A$2:$A$2000,all!$A2)>0,"bad","")

hth,

Doug

 

[theirieone]

Hey Doug,

I tried pasting this formula but it ended up not flagging anything at
all. The only thing I changed were the ranges a bit. Here's what I
have:

=IF(COUNTIF(bad!$A$1:$A$1307,all!$A1)>0,"bad","")

any thoughts?

 

[theirieone]

Hello Jim,

I tried out your below code and I get a "Compile Error: Invalid or
Unqualified Reference" error message and it highlights the first
instance of the ".Cells" text in your first "Set" below. Any ideas?

 

[Doug Glancy]

Rich,

Just to make sure we're doing the same thing, the bad email addresses are on
a sheet named "bad" in cells A1 to A1307. The ones to be tested are on a
sheet named "all" in column A. The formula was copied down so that the row
references to "all" are relative. For example, the formula in All!B2 should
be:

=IF(COUNTIF(bad!$A$1:$A$1307,all!$A2)>0,"bad","")

Notice that the "all!$A1" in the first row formula has changed to "all!$A2".
In other words, the formula is always looking at the address in the adjacent
cell and looking to see if it matches any addresses on "bad".

If that's all true, I'm not sure what else to check, unless of course there
are no bad email addresses in "all" ...

hth,

Doug