Advanced Conditional Formatting: If Any Word GREATER than 1 Character in a cell, can be found within another cell.

[jeffcoleky]

I can't wrap my brain around this scenario to come up with something.

Spelled out this is what I would like conditional formatting to do: Highlight a row if ANY word in A2 greater than 1 character, can be found within cell B2.

Why we need it: We're trying to quickly identify which names in Columns A & B don't match up, that's all.

• The reason we want any word 2 characters or more, is because we don't want to match middle initials.
• The reason we are OK with matchin ANY word in column "A" and not just the first or last names is because if the person was married, divorced, etc. then their last name could be different (eg. Pat Smith -> Pat Jones) but they're still the same person. If they go by a shortened name (Patrick -> Pat) in one column and not the other, then we still want it to match. This won't be an exact science though because if a female divorces and goes by a shortened first name AND changes her last name, it won't find her (EG. Patricia Smith -> Pat Jones), but that's OK.

Does this make sense?

Here is my sample data:

Excel 2010

	A	B	C
Seaches These words	Within Same Row of this Column
PAT SMITH	PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC
PAT JONES	PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC
PATRICIA SMITH	PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC
PATRICIA JONES	PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC
BOB & SARA SMITH	SMITHIE, BOBBIE; SMITHIE, JANET
GUELDA tommy	GUELDA, TOMMY
MMCCI LLC	TORSTRICK, SAMUEL; TORSTRICK, SAMUEL, W; MMCCI LLC
WITTMER Chris J & Sandy J	WITTMER, CHRIS, J; WITTMER, SANDY, J; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT
DOWNIE TONI	DOWNIE, TONI; DESOKY, JOE, H; NATIONAL CHECK BUREAU INC; SOUTHERN EMERGENCY MEDICAL SPE, CIALISTS; FAYETTE COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS
TERRA JONES	EDDINGTON, CHRISTIE; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK KENTUCKY; TERRA FUNDING INC; TERRA FUNDING LLC; COUNTY INVESTMENTS LLC
JEFF TATE	STRANGE, JAMES; POORE, JAN; JOE PROPERTIES LLC; SMW LOCAL 110 FEDERAL CREDIT U, NION; HANNIN, GREG, TATE; HANNIN, TATE; SHEFFIELD, DON
HAMPTON Jeff L II	HAMPTON, JEFF, L II; HAMPTON, JEFF; HAMPTON, JEFF, II; CAPITAL ONE BANK USA NA; ECLIPSE BANK INC; FRANKFORT FRANKLIN COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS
BATTS Janet	MASON, JANET; MASON, TONY; MY HEALTHCARE INC; COMMONWEALTH OF INDIANA WORKF, ORCE DEVELOPMENT CAB, INET EX REL DIVISION; FIRST UNION NATIONAL BANK OF N, ORTH CAROLINA; WACHOVIA BANK OF DELAWARE NATI, ONAL ASSN; CHRYSLER FINANCIAL; TD AUTO FINANCE LLC
BRADEN LESLEY Mary	BRADEN, LESLEY, MARY; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT
WILLIAM & ALEXANDRA CRUMS LAND TRUST	SMITH, SHANNON; SMITH, TARISA, LYNETTE FULLER; FULLER, TARISA

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

[TD="align: center"]Exists (Manual Result)[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

Formatting Constants:

• The words in Column "A" are ALWAYS separated by spaces or an "&". Never a comma or semicolon
• The words in Column "B" are ALWAYS separated by a comma or a space. Each name is always separated by a semicolon ";".

I've tried all the ISNUMBER, SEARCH, and FIND variations I can think of... but what I need is more complicated than I can figure out. I'm sure what i need is a complex version of the below formulas:
=ISNUMBER(SEARCH(A2,B2))
=ISNUMBER(FIND(A2,B2))

I'm OK adding a macro or a column to the sheet if needed.

I know how to set the conditional formatting rule up, I just need help with the formula. Anyone?

 

[circledchicken]

Hi,

I'm not sure you're sample output matches your description because for example:
-> In row 3, there is a partial match for PAT
-> In row 6, there are partial matches for BOB and SMITH

If your happy adding extra columns, one option you can try is to:
-> Use Text to Columns to split out the entries in column A using a single space as a delimeter
-> Apply one of the following formulas

Method 1 - allowing for partial matches (using CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

=ISNUMBER(
    LOOKUP(
      9.99999999999999E+307,
      IF(LEN(D2:I2) > 1, SEARCH(D2:I2, A2))))

Method 2 - not allowing for partial matches, but returns the results shown in your sample output (also requires CTRL-SHIFT-ENTER):

=ISNUMBER(
    LOOKUP(
      9.99999999999999E+307,
      IF(
        LEN(D2:I2) > 1,
        SEARCH(
          "|" & D2:I2 & "|",
          "|" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", "|"), ",", ""), ";", "") & "|"))))

Here is the sample output from both methods (note I have moved the split data from column A to columns D:I):

Excel 2013

	A	B	C	D	E	F	G	H	I
Within Same Row of this Column	Exists (Method 1)	Exists (Method 2)	Seaches These words
PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC	PAT	SMITH
PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC	PAT	JONES
PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC	PATRICIA	SMITH
PATRICIA SMITH; EDDINGTON, BETSY; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK INDIANA; TERRAS FUNDING INC; TERRAS FUNDING LLC; COUNTY LINE INVESTMENTS LLC	PATRICIA	JONES
SMITHIE, BOBBIE; SMITHIE, JANET	BOB	&	SARA	SMITH
GUELDA, TOMMY	GUELDA	tommy
TORSTRICK, SAMUEL; TORSTRICK, SAMUEL, W; MMCCI LLC	MMCCI	LLC
WITTMER, CHRIS, J; WITTMER, SANDY, J; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT	WITTMER	Chris	J	&	Sandy	J
DOWNIE, TONI; DESOKY, JOE, H; NATIONAL CHECK BUREAU INC; SOUTHERN EMERGENCY MEDICAL SPE, CIALISTS; FAYETTE COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS	DOWNIE	TONI
EDDINGTON, CHRISTIE; CAPITAL ONE BANK; AMERICAN GENERAL FINANCIAL SER, VICES INC; AMERICAN GENERAL FINANCE; SEARS ROEBUCK & CO; NATIONAL CITY BANK KENTUCKY; TERRA FUNDING INC; TERRA FUNDING LLC; COUNTY INVESTMENTS LLC	TERRA	JONES
STRANGE, JAMES; POORE, JAN; JOE PROPERTIES LLC; SMW LOCAL 110 FEDERAL CREDIT U, NION; HANNIN, GREG, TATE; HANNIN, TATE; SHEFFIELD, DON	JEFF	TATE
HAMPTON, JEFF, L II; HAMPTON, JEFF; HAMPTON, JEFF, II; CAPITAL ONE BANK USA NA; ECLIPSE BANK INC; FRANKFORT FRANKLIN COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS	HAMPTON	Jeff	L	II
MASON, JANET; MASON, TONY; MY HEALTHCARE INC; COMMONWEALTH OF INDIANA WORKF, ORCE DEVELOPMENT CAB, INET EX REL DIVISION; FIRST UNION NATIONAL BANK OF N, ORTH CAROLINA; WACHOVIA BANK OF DELAWARE NATI, ONAL ASSN; CHRYSLER FINANCIAL; TD AUTO FINANCE LLC	BATTS	Janet
BRADEN, LESLEY, MARY; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT	BRADEN	LESLEY	Mary
SMITH, SHANNON; SMITH, TARISA, LYNETTE FULLER; FULLER, TARISA	WILLIAM	&	ALEXANDRA	CRUMS	LAND	TRUST

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet1​

 

[Marcelo Branco]

Or maybe a UDF

Function FindString(str1 As String, str2 As String) As String
    Dim spl As Variant, bFound As Boolean, i As Long, strAux As String
    
    strAux = Replace(str2, ";", " ")
    strAux = Replace(strAux, ",", " ")
    spl = Split(str1, " ")
    
    For i = 0 To UBound(spl)
        If Len(spl(i)) > 1 And InStr(1, " " & strAux & " ", " " & spl(i) & " ", vbTextCompare) Then
            bFound = True
            Exit For
        End If
    Next i
        
    If bFound Then
        FindString = "Yes"
    Else
        FindString = "No"
    End If
        
        
End Function

Usage

C2
=FindString(A2,B2)

copy down

M.

 

[jeffcoleky]

circledchicken: Partial matches are NOT ok. Pat in A could not show a positive match if PATRICIA was in B. That is why rows 3 & 6 are both no's. I'm OK adding ONE column, not multiple...unless I absolutely have to. Is there a cleaner way to do it?

 

[jeffcoleky]

Or maybe a UDF

Function FindString(str1 As String, str2 As String) As String
    Dim spl As Variant, bFound As Boolean, i As Long, strAux As String
    
    strAux = Replace(str2, ";", " ")
    strAux = Replace(strAux, ",", " ")
    spl = Split(str1, " ")
    
    For i = 0 To UBound(spl)
        If Len(spl(i)) > 1 And InStr(1, " " & strAux & " ", " " & spl(i) & " ", vbTextCompare) Then
            bFound = True
            Exit For
        End If
    Next i
        
    If bFound Then
        FindString = "Yes"
    Else
        FindString = "No"
    End If
        
        
End Function

Usage

C2
=FindString(A2,B2)

copy down

M.

Edited code of sheet, pasted your function, used your formula, and i still get a "#NAME?" error... am i doing something wrong?

 

[circledchicken]

circledchicken: Partial matches are NOT ok. Pat in A could not show a positive match if PATRICIA was in B. That is why rows 3 & 6 are both no's. I'm OK adding ONE column, not multiple...unless I absolutely have to. Is there a cleaner way to do it?

Marcelo's UDF in post #3 is the way to go then for a concise full match.

You need to put the code in a regular module (through Insert -> Module) rather than a Sheet module. See here for more detail:
http://www.contextures.com/xlvba01.html#Worksheet

 

[Marcelo Branco]

You need to put the code in a regular module (through Insert -> Module) rather than a Sheet module. See here for more detail:
http://www.contextures.com/xlvba01.html#Worksheet

Ditto!

Alt+F11 to Open the VBA Editor
Insert > Module
Paste the code in the right panel

M.

 

[jeffcoleky]

Wow, is it really that simple? It seems to work fine now that I added it properly. Thanks to you both.

 

[markmzz]

Another way (with formula):

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

In C2

=IF(COUNT(SEARCH(IF(LEN(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(ROW($1:$999)-1)*LEN(A2)+1,LEN(A2))))>1,
" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(ROW($1:$999)-1)*LEN(A2)+1,LEN(A2)))&" ")," "&SUBSTITUTE(SUBSTITUTE(B2,";"," "),","," ")&" ")),"Yes","No")

Markmzz

 

[circledchicken]

Nice single formula option Markmzz!