CODEWITHSUNDEEP
vbaexcel
Sean Reyes
Sean Reyes

Reputation: 1643

EXCEL VBA COMPARING 2 COLUMN

[enter image description here I have 2 list with firstname and Lastname, now I want to know if someone in list1 exist at the list2 (References)

this is the Code that I'm using Right Now but the problem is (Picture3 References)

Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant

    Set CompareRange = Range("E1:F6")

    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub

I want A result Like (Picture4 References)

where I only Get to those whose Firstname and Lastname are The same

NOTE: At (picture 3 & 4) my code change

  Set CompareRange = Range("G2:H7")

Upvotes: 1

Views: 112

Answers (2)

Siva
Siva

Reputation: 1149

You can try this as well

Sub Find_Matches()
    Dim list1 As Range
    Dim list2 As Range
    Set list1 = Range("A1:B6")
    Set list2 = Range("E1:F6")

    For Each row1 In list1.Rows
        For Each row2 In list2.Rows
            If (row1.Cells(1) = row2.Cells(1) And row1.Cells(2) = row2.Cells(2)) Then
            row1.Cells(1).Offset(0, 2) = row1.Cells(1)
            row1.Cells(2).Offset(0, 2) = row1.Cells(2)
            Exit For
            End If
        Next row2

    Next row1

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

No need for VBA

Let's say your worksheet looks like this

enter image description here

Put this formula in cell C1 

=IF(ISERROR(MATCH(A1&"|"&B1,$E$1:$E$6&"|"&$F$1:$F$6,0)),"",A1)

and press CTRL + SHIFT + ENTER and drag the formula down.

Put this formula in cell D1 and pull the formula down

=IF(C1<>"",B1,"")

This is the end result

enter image description here

Logic:

  1. We are performing a two-column lookup using the & operator
  2. We are using "|" to prevent any false positives. For example "Name1N" Col A and "ame2" in Col B which after concatenating would be the same as that for "Name1" Col A and "Name2"
  3. In Col D we are just checking if Col C is populated. If it is then simply pull the values from Col B
  4. The first formula is an array formula and hence we are using CTRL + SHIFT + ENTER in lieu of just ENTER

Upvotes: 3

Related Questions