Comparing data in 3 columns on a Microsoft Excel spreadsheet

Registration date
Tuesday March 17, 2015
Last seen
March 17, 2015
 daniz39 -
Would be really grateful for some help.

I have 3 columns of different lengths with email addresses in them and I want to see all instances where the same email address appears in all 3 columns. Can't work out how to do it though.

Any advice much appreciated. Thanks.

1 reply

Registration date
Sunday September 12, 2010
Last seen
November 22, 2021
Hi Daniz,

The following code will loop through column A and compare them to columns B and C. If a value from column A is found in both column B and C then the value is pasted in the next available cell of column D.

Check the column references in the code and adjust them to suit your situation. Let us know if you get stuck.

Here is the code:
Sub RunMe()
Dim cFind As Range
Dim IsThere As Boolean
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lRow)
    Set cFind = Columns("B:B").Find(cell.Value)
    If Not cFind Is Nothing Then IsThere = True
    Set cFind = Columns("C:C").Find(cell.Value)
    If Not cFind Is Nothing And IsThere = True Then
        IsThere = True
        IsThere = False
    End If
    If IsThere = True Then Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Value
Next cell
End Sub

Best regards,

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Thank you so much for taking the time to reply with such a detailed answer. I'll give it a go!