How to Compaire 3 lists of Names

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I want to compaire 2 name lists (Fist Name and Last Name) with a third list contain the full name. And the result should come with either "Full Match" if both first name and last name found the in the third list, " Partial Match" if only one either first name or last name found the in third list and "Clear" if there are no match at all.

Example

A B C D
First Name Last Name Full Name Result
Tom Atcha Edward Atcha Partial Match
Edward Atcha Adam Taha Full Match
Safaa Hasan Mariam Nori Clear


Many thank in advance for any help

Cheers




1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try this macro . the result is in column E. check this with column D.finally you can delete column D

But there are a few more possibilities
you can modify the macro if you desire

Sub TEST()
Dim rng As Range, c As Range, j As Integer, k As Integer, x As String, cfind As Range
Range("E1").EntireColumn.Delete
Set rng = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In rng
x = c & " " & c.Offset(0, 1)
j = WorksheetFunction.Search(" ", c.Offset(0, 2))
k = Len(c.Offset(0, 2))
Set cfind = Columns("C:C").Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
c.End(xlToRight).Offset(0, 1) = "full match"
GoTo nnext
End If
If c = Left(c.Offset(0, 2), j - 1) Or c.Offset(0, 1) = Right(c.Offset(0, 2), k - j) Then
c.End(xlToRight).Offset(0, 1) = "partial match"
GoTo nnext
End If
If c <> Left(c.Offset(0, 2), j - 1) And c.Offset(0, 1) <> Right(c.Offset(0, 2), k = j) Then
c.End(xlToRight).Offset(0, 1) = "no match"
End If
nnext:
Next c
End Sub