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
1	Seaches These words	Within Same Row of this Column	Exists (Manual Result)
2	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	Yes
3	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	No
4	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	Yes
5	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	Yes
6	BOB & SARA SMITH	SMITHIE, BOBBIE; SMITHIE, JANET	No
7	GUELDA tommy	GUELDA, TOMMY	Yes
8	MMCCI LLC	TORSTRICK, SAMUEL; TORSTRICK, SAMUEL, W; MMCCI LLC	Yes
9	WITTMER Chris J & Sandy J	WITTMER, CHRIS, J; WITTMER, SANDY, J; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT	Yes
10	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	Yes
11	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	Yes
12	JEFF TATE	STRANGE, JAMES; POORE, JAN; JOE PROPERTIES LLC; SMW LOCAL 110 FEDERAL CREDIT U, NION; HANNIN, GREG, TATE; HANNIN, TATE; SHEFFIELD, DON	Yes
13	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	Yes
14	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	Yes
15	BRADEN LESLEY Mary	BRADEN, LESLEY, MARY; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT	Yes
16	WILLIAM & ALEXANDRA CRUMS LAND TRUST	SMITH, SHANNON; SMITH, TARISA, LYNETTE FULLER; FULLER, TARISA	No

<tbody>
</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
1	Within Same Row of this Column	Exists (Method 1)	Exists (Method 2)	Seaches These words
2	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	TRUE	TRUE	PAT	SMITH
3	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	TRUE	FALSE	PAT	JONES
4	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	TRUE	TRUE	PATRICIA	SMITH
5	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	TRUE	TRUE	PATRICIA	JONES
6	SMITHIE, BOBBIE; SMITHIE, JANET	TRUE	FALSE	BOB	&	SARA	SMITH
7	GUELDA, TOMMY	TRUE	TRUE	GUELDA	tommy
8	TORSTRICK, SAMUEL; TORSTRICK, SAMUEL, W; MMCCI LLC	TRUE	TRUE	MMCCI	LLC
9	WITTMER, CHRIS, J; WITTMER, SANDY, J; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT	TRUE	TRUE	WITTMER	Chris	J	&	Sandy	J
10	DOWNIE, TONI; DESOKY, JOE, H; NATIONAL CHECK BUREAU INC; SOUTHERN EMERGENCY MEDICAL SPE, CIALISTS; FAYETTE COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS	TRUE	TRUE	DOWNIE	TONI
11	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	TRUE	TRUE	TERRA	JONES
12	STRANGE, JAMES; POORE, JAN; JOE PROPERTIES LLC; SMW LOCAL 110 FEDERAL CREDIT U, NION; HANNIN, GREG, TATE; HANNIN, TATE; SHEFFIELD, DON	TRUE	TRUE	JEFF	TATE
13	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	TRUE	TRUE	HAMPTON	Jeff	L	II
14	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	TRUE	TRUE	BATTS	Janet
15	BRADEN, LESLEY, MARY; FRANKFORT FRANKLIN COUNTY ME, TRO GOVERNMENT	TRUE	TRUE	BRADEN	LESLEY	Mary
16	SMITH, SHANNON; SMITH, TARISA, LYNETTE FULLER; FULLER, TARISA	FALSE	FALSE	WILLIAM	&	ALEXANDRA	CRUMS	LAND	TRUST

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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!