First & Last Name in a Single Cell Comparison Checker

[censo]

Greetings,

I have two columns (A:B) with First and Last names present in each column and I'd like to construct a function in column C that compares the entire cell contents from B:A and only indicates when it cannot discern a match for a given row.

Additional Details

- I do not require the names be parsed out into their own columns.
- If either the first or last name is detected, the result is considered a match
- If neither first or last name is detected, the result is considered a mismatch and outputs an "N"
- Middle names or initials are sometimes present. Middle names can be evaluated but initials should be ignored (so, any characters >1)
- The key cell is B, to which A will be compared for determining a mismatch (B-->A)

Basically, if any component of a name can be detected (except a single character Middle Initial), it is considered a match and does nothing (blank value)

Diagram below shows intended output

If the below is not possible, I'd entertain the next best solution that gets me close.

Thanks in advance.

- C

Book1
	A	B	C
1	Name A	Name B	Match?
2	Sara Lee	Sara Lee
3	S Lee	Sara Lee
4	Sara M Lee	S M Lee
5	Sara L	S Lee	N
Sheet1

 

[kweaver]

I haven't figured out handling the middle name or initial, but I think this does the first/last name match or not.

continents.xlsx
	A	B	C
1	Name A	Name B	Match?
2	Sara Lee	Sara Lee	Y
3	S Lee	Sara Lee	Y
4	Sara M Lee	S M Lee	Y
5	Sara L	S Lee	N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(OR(LEFT(FIND(" ",A2)-1)=LEFT(FIND(" ",B2)-1),(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1)))=(RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))),1)))),"Y","N")

 

[tonyyy]

Another approach...

Function Compare(nameB As Range, nameA As Range) As String
Dim arrB As Variant, arrA As Variant
Dim i As Long, j As Long

arrB = Split(nameB.Value)
arrA = Split(nameA.Value)
For i = 0 To UBound(arrB)
    For j = 0 To UBound(arrA)
        If arrB(i) = arrA(j) And Len(arrB(i)) > 1 Then
            Compare = ""
        Else
            Compare = "N"
        End If
    Next j
Next i
End Function

Happy Holidaze!

Tony

 

[Phuoc]

Book1
	A	B	C
1	Name A	Name B	Match?
2	Sara Lee	Sara Lee	Y
3	S Lee	Sara Lee	Y
4	Sara M Lee	S M Lee	Y
5	Sara L	S Lee	N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(COUNTIF(B2,SUBSTITUTE(A2," ","*"))+COUNTIF(A2,SUBSTITUTE(B2," ","*")),"Y","N")

 

[censo]

I haven't figured out handling the middle name or initial, but I think this does the first/last name match or not.

continents.xlsx
	A	B	C
1	Name A	Name B	Match?
2	Sara Lee	Sara Lee	Y
3	S Lee	Sara Lee	Y
4	Sara M Lee	S M Lee	Y
5	Sara L	S Lee	N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(OR(LEFT(FIND(" ",A2)-1)=LEFT(FIND(" ",B2)-1),(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1)))=(RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))),1)))),"Y","N")

Thanks for your prompt reply.

As you alluded to, whenever a single name or just an initial is detected, I receive the #VALUE! error

Book1
	A	B	C
1	Name A	Name B	Match?
2	Sara Lee	Sara Lee	Y
3	Lee	Sara Lee	#VALUE!
4	Sara M Lee	S M Lee	Y
5	Sara L	S	#VALUE!
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(OR(LEFT(FIND(" ",A2)-1)=LEFT(FIND(" ",B2)-1),(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1)))=(RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))),1)))),"Y","N")

 

[censo]

Book1
	A	B	C
1	Name A	Name B	Match?
2	Sara Lee	Sara Lee	Y
3	S Lee	Sara Lee	Y
4	Sara M Lee	S M Lee	Y
5	Sara L	S Lee	N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(COUNTIF(B2,SUBSTITUTE(A2," ","*"))+COUNTIF(A2,SUBSTITUTE(B2," ","*")),"Y","N")

Thanks for your contribution

I observed an anomaly where if just a single name is detected, the function returns a false positive (e.g., Row 2 below should be a match because even though the last name "Lee" is not present in Column A, there is a successful match of "Sara" found in both cells.

Can a slight modification to your function achieve the desired outcome?

Book1
	A	B	C
1	Name A	Name B	Match?
2	Sara	Sara Lee	N
3	S Lee	Sara Lee	Y
4	Sara M Lee	S M Lee	Y
5	Sara L	S Lee	N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(COUNTIF(B2,SUBSTITUTE(A2," ","*"))+COUNTIF(A2,SUBSTITUTE(B2," ","*")),"Y","N")

 

[censo]

Another approach...

Function Compare(nameB As Range, nameA As Range) As String
Dim arrB As Variant, arrA As Variant
Dim i As Long, j As Long

arrB = Split(nameB.Value)
arrA = Split(nameA.Value)
For i = 0 To UBound(arrB)
    For j = 0 To UBound(arrA)
        If arrB(i) = arrA(j) And Len(arrB(i)) > 1 Then
            Compare = ""
        Else
            Compare = "N"
        End If
    Next j
Next i
End Function

Happy Holidaze!

Tony

Thanks for your contribution, Tony. Nice play on words ... I saw what you did there (e.g., "Holidaze")

I would love to try out your proposed solution; however, I provided a simple spreadsheet as an example and I'm not experienced enough in VB to know how to modify the code to fit into my real-world environment.

Therefore, if you can modify your code based on the below parameters, it would be much appreciated.

Relative to the table I originally posted:

B = AU (main data column)
A = AO (column to compare against main data column, aka AU -->AO)
C = BF (comparison result output column, aka "N")

Please note I do not wish an output of "Y" if a successful match is made. Successful matches do nothing (return blank value). I only wish to evaluate the "N" results

Thanks

 

[Phuoc]

Book1
	A	B	C
1	Name A	Name B	Match?
2	Sara	Sara Lee	Y
3	S Lee	Sara Lee	Y
4	Sara M Lee	S M Lee	Y
5	Sara L	S Lee	N
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(COUNTIF(B2,SUBSTITUTE(A2," ","*"))+COUNTIF(A2,SUBSTITUTE(B2," ","*"))+ISNUMBER(FIND(A2,B2))+ISNUMBER(FIND(B2,A2)),"Y","N")

 

[tonyyy]

"I'd like to construct a function..."

Well, I took you literally when you stated you wanted to "construct a function". The code provided does just that, and does not need to be modified to accommodate your real-world environment.

If you need guidance on how to copy/paste the code into your workbook, please reference How to Add Code to Excel Workbook and click on the Regular Module link.

To use the Compare function, select a cell in Column BF - I imagine BF2 would be appropriate - then click the Insert Function tool (to the left of the Formula Bar). Choose "User Defined" from the drop down menu next to the prompt "Or select a category:" Select the Compare function and click OK. A dialog box will appear where you can select two cells to compare - from Column AU and Column AO. Click OK and the code will return a blank cell if there's a match and an N if there's no match. Like any Excel function, you can copy/paste or click/drag the function in BF2 down the remainder of the column.

 

[tonyyy]

A modification is required to accommodate the case of a single name...

Function Compare(nameB As Range, nameA As Range) As String
Dim arrB As Variant, arrA As Variant
Dim i As Long, j As Long

arrB = Split(nameB.Value)
arrA = Split(nameA.Value)
For i = 0 To UBound(arrB)
    For j = 0 To UBound(arrA)
        If arrB(i) = arrA(j) And Len(arrB(i)) > 1 Then
            Compare = ""
            Exit Function
        Else
            Compare = "N"
        End If
    Next j
Next i
End Function