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

Question

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.

TrowaD · Answer

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.

Comparing data in 3 columns on a Microsoft Excel spreadsheet

1 response

Subscribe To Our Newsletter!