Comparing data in 3 columns on a Microsoft Excel spreadsheet [Solved/Closed]

Report
Posts
1
Registration date
Tuesday March 17, 2015
Status
Member
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

Posts
2618
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 25, 2020
418
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
    Else
        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,
Trowa

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!