Compare two columns and mark differences in third

[elninohaginho]

Hello Excel Masters,

I am trying to compare records in column A and B and mark in column C the found entries (1 for found, 0 for not found).

My basic task is the following:

In column A I have e-mails of people who were invited to a meeting, in column B I have e-mails of all the people who should have attended it and need to mark if they were present or not.

They way I would perform this with a formula would be the following (pasted in column C):

=IF(ISERROR(MATCH(A2,$B$2:$B$100000,0)), 0,1)

I don't want to use formulas, but VBA and found this little bit of code that almost does what I want:

Sub test()
Dim a, i As Long, b(), n As Long, x
a = Range("a1").CurrentRegion.Resize(,2).Value
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a,1)
If (Not IsEmpty(a(i,1))) * (Not .exists(a(i,1))) Then .add a(i,1), Nothing
Next
ReDim b(1 To UBound(a,1), 1 To 1)
For i = 1 To UBound(a,1)
If Not IsEmpty(a(i,2)) Then
If Not .exists(a(i,2)) Then
n = n + 1 : b(n,1) = a(i,2)
Else
.remove a(i,2)
End If
End If
Next
x = .keys
End With
With Range("d1")
.CurrentRegion.ClearContents
.Resize(,2).Value = [{"Not in A", "Not in B"}]
With .Offset(1)
If n > 0 Then .Resize .Value = b
End With
On Error Resume Next
.Offset(1,1).Resize(.Count).Value = Application.Transpose(x)
End With
End Sub

The issue with this code is that instead of marking by the specific entry in column C, it creates lists of records that are missing from A in one column and records that are missing from B in another.

Can anyone please point me to a method I could apply in this scenario? I am still very much a beginner to VBA and imagine it should be two loops, one going through column A, grabbing a record, looping through column B and applying a value to offset cell when found, but I can't figure out how to translate it to the code and will be most thankful for any advice on the topic.

 

[Fluff]

No need for loops, you can use your formula like

Sub Check()
With Range("C2", Range("B" & Rows.Count).End(xlUp).Offset(, 1))
   .Value = Evaluate(Replace("IF(ISERROR(MATCH(@,$B$2:$B$100000,0)), 0,1)", "@", .Offset(, -2).Address))
End With
End Sub

 

[elninohaginho]

It is so simple, yet so elegant. You deserve a medal, thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback