Previous Topic Next Topic

How to Compaire 3 lists of Names

Closed
Hasan - Jan 15, 2011 at 08:07 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 15, 2011 at 10:12 PM
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




Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 15, 2011 at 10:12 PM
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
0