Compare text between two cols

[Panchistiko]

Hello everyone,

I have these columns with about 50000 names, and I need to determine whether the SIMPLE NAME matches the FULL NAME, with at least two words, in any order.
In col C, I’d expect a “YES” if the names match, otherwise a “NO”.
If match just one word, it’s a “NO”

Book1
	A	B	C
1	FULL NAME	Simple Name	Match
2	GONZALO MARCO PEREZ .	Gonzalo M Perez	Yes
3	RUBEN ARMENIO ESPINOZA ESTERN	Espinoza Ruben	Yes
4	DIANA MARIA DIAZ AGUILERA	Diana M Diaz	Yes
5	CARLA RAMIRA HERNANDEZ LOZ	Claudia R Hernandez	No
6	MARGARITA FERRER COVARRUBIAS	Ferrer Monica	No
7	ANA LUIS COLONNA LORENZINI	Colonna Ana	Yes
8	DIEGA YOLANDA AMAYA BOLOA	Diana Amaya	No
9	LIDIA CARMEN HINCAPIE VENEGAS	Lidia Hincapie	Yes
Sheet1

Thank you all.

 

[JoeMo]

Here's a UDF (lightly tested) you can try with an example below.

Excel Workbook
	A	B	C
1	FULL NAME	Simple Name	Match
2	GONZALO MARCO PEREZ .	Gonzalo M Perez	Yes
3	RUBEN ARMENIO ESPINOZA ESTERN	Espinoza Ruben	Yes
4	DIANA MARIA DIAZ AGUILERA	Diana M Diaz	Yes
5	CARLA RAMIRA HERNANDEZ LOZ	Claudia R Hernandez	No
6	MARGARITA FERRER COVARRUBIAS	Ferrer Monica	No
7	ANA LUIS COLONNA LORENZINI	Colonna Ana	Yes
8	DIEGA YOLANDA AMAYA BOLOA	Diana Amaya	No
9	LIDIA CARMEN HINCAPIE VENEGAS	Lidia Hincapie	Yes
Sheet6

To install the Function:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function MatchTwo(Full As String, Simple As String) As String
Dim B As Boolean, S1 As Variant, S2 As Variant, ct As Long, i As Long, j As Long
S1 = Split(Full, " ")
S2 = Split(Simple, " ")
For i = LBound(S1) To UBound(S1)
    For j = LBound(S2) To UBound(S2)
        If LCase(S2(j)) = LCase(S1(i)) Then
            ct = ct + 1
            If ct = 2 Then
                B = True
                Exit For
            End If
        End If
    Next j
    If B Then
        MatchTwo = "Yes"
        Exit Function
    End If
Next i
If Not B Then MatchTwo = "No"
End Function

 

[DRSteele]

If you wish to have formulas, this will suffice. Copy C2 downward.

I would like to note that you might encounter some problems with your parameters. For example, what would you do if Gonzalo Y Perez was the Simple Name - would that be a match or not?

	A	B	C
FULL NAME	Simple Name	Match
GONZALO MARCO PEREZ	Gonzalo M Perez
RUBEN ARMENIO ESPINOZA ESTERN	Espinoza Ruben
DIANA MARIA DIAZ AGUILERA	Diana M Diaz
CARLA RAMIRA HERNANDEZ LOZ	Claudia R Hernandez
MARGARITA FERRER COVARRUBIAS	Ferrer Monica
ANA LUIS COLONNA LORENZINI	Colonna Ana
DIEGA YOLANDA AMAYA BOLOÑA	Diana Amaya
LIDIA CARMEN HINCAPIE VENEGAS	Lidia Hincapie

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #A9D08E"]Yes[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #A9D08E"]Yes[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #A9D08E"]Yes[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #A9D08E"]No[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #A9D08E"]No[/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #A9D08E"]Yes[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #A9D08E"]No[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #A9D08E"]Yes[/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(LEFT(B2,SEARCH(" ",B2)-1),A2)+SEARCH(IF(ISERROR(MID(B2,SEARCH(" * ",B2),3)),MID(B2,SEARCH(" ",B2)+1,100),MID(B2,SEARCH(" * ",B2)+3,100)),A2)),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Panchistiko]

Hi DRSteel,
Thank you for your reply.

It worked out pretty well, but, how come this examples returned a "NO", when they do match.

Book1
	A	B	C
1	FULL NAME	SIMPLE NAME	MATCH
2	MONICA DIANA PIRRAS CARPACCIO	Monica Diana Pirras Carpaccio	No
3	MARIANA ALBERTA HERNANDEZ .	Mariana Hernandez [Manager - PE]	No
4	YAMIL LUTIO CABALLOS MANODA .	Yamil Lutio Caballos [Tienda 2 - CL]	No
5	DIEGO LUZ MARCHANT .	Diego Luiz Marchant [Tienda X - CO]	No
Sheet2

 

[Panchistiko]

Thank you JoeMo.... this worked great.

 

[DRSteele]

Why does it return "No" now? Because your Simple Name format has changed radically from your original example: now you've got some with four words, some with middle names instead of just a space-initial-space and some with a plethora of spaces.

I don't even know how to tackle this complex task. VBA from a genius is what you'll need.

 

[JoeMo]

Thank you JoeMo.... this worked great.

You are welcome - thanks for the reply.