Search code examples
vbaexcelexcel-formulaworksheet-function

Comparing one column of cells to another

I have a list of 20,000+ email addresses in one column and a similarly sized column of emails I want to compare. I want to flag entries in the first column that also appear in the second. I wrote this code to make it happen:

Sub macro()

Dim i As Long
Dim n As Long

i = 2

For i = 2 To 27934

n = 2

For n = 2 To 20824

If Cells(i, 1) = Cells(n, 7) Then
    Cells(i, 3) = 1

End If

Next n

Next i

End Sub

It's telling me 'type mismatch'. I tried a few other options, like a while loop. I'm sure there are a million other ways to get this done, but I can't figure it out. Anybody got a minute to help?

Solution

  • If data is in ColumnsA (to be flagged) and B, starting in Row2: 

    =IFERROR(MATCH(A2,B:B,0),"")

    in C2 and copied down should "get the job done", and relatively quickly.