Comparing data in 3 columns on a Microsoft Excel spreadsheet

daniz39 Posts 1 Registration date Tuesday March 17, 2015 Status Member Last seen March 17, 2015 - Mar 17, 2015 at 11:08 AM
 daniz39 - Mar 23, 2015 at 01:16 PM
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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 23, 2015 at 01:12 PM
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!