Compare two columns to two other columns

[austenr]

I have 4 columns. Columns A & B have last name, first name respectfully. Columns C & D have Last name, First name.

I need to be able to compare columns A & B to C & D for matching. I know how to match one column to one column but do not know how to match two columns to two columns. Can anyone start me out?

[Jacob Hilderbrand]

You can combine the cells, for example.


If(A1&B1 = C1&D1, True, False)

[austenr]

That is a good idea!! I have done a little more thinking and investigating and found I need to do a fuzzy match. For instance Joe Brown and Joseph Brown could be the same person. Chris, Christopher, etc. Have been looking on line and can't seem to find something that can do the trick. Any suggestions? Thanks

[Jacob Hilderbrand]

Ok, here is some wonderful code from Juan Pablo over at MrExcel.com. I slightly modified to to work with a range of values (inside a loop) instead of just one set of values.

The way it is currenly set up, we have string1 as the values in column A and string2 as the values in column B. The destination for the match percent will go in column C.

You will need to first combine your data from 4 cells to two. You can do that with a simple formula like =A1&B1 then fill down.



Option Explicit

Sub FuzzyLoop()
Dim i As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
Call FuzzyMatch(Range("A" & i), Range("B" & i), Range("C" & i))
Next i
End Sub

Sub FuzzyMatch(String1 As Range, String2 As Range, Destination As Range)
Dim i1 As Long
Dim j1 As Long
Dim i As Long
Dim TopString As String
Dim TopMatch As String
Dim Str1 As String
Dim Str2 As String
Dim Ar1() As Long
Dim Fn As WorksheetFunction
Dim f As Long
Set Fn = WorksheetFunction
Str1 = String1.Text
Str2 = String2.Text
ReDim Ar1(Len(Str1)) As Long
On Error Resume Next
For i1 = 1 To Len(Str1)
For i = 1 To Len(Str1)
Ar1(i) = 0
Next i
TopString = ""
i = 1
f = Fn.Search(Mid(Str1, i1, 1), Str2)
If Not IsEmpty(f) Then
TopString = IIf(1 >= Len(TopString), Mid(Str1, i1, 1), TopString)
Ar1(i) = f
i = i + 1
f = Empty
For j1 = i1 + 1 To Len(Str1)
f = Fn.Search(Mid(Str1, j1, 1), Str2, Ar1(i - 1))
If Not IsEmpty(f) Then
If f > Ar1(i - 1) Then
TopString = IIf(Len(TopString & Mid(Str1, j1, 1)) _
>= Len(TopString), TopString & Mid(Str1, j1, 1), TopString)
Ar1(i) = f
i = i + 1
End If
f = Empty
End If
Next j1
End If
TopMatch = IIf(Len(TopMatch) < Len(TopString), TopString, TopMatch)
Next i1
Destination.Value = Fn.Text(Len(TopMatch) / Len(Str1), "0.00%")
End Sub