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?

 

[jeffcoleky]

Yes, thank you!

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

 

[bschwartz]

Markmzz = King of formulas.

 

[markmzz]

Yes, thank you!

You're welcome.

Markmzz

 

[markmzz]

Markmzz = King of formulas.

Thanks for the nice words, but I think that we are all learning here in the fórum.

Markmzz

 

[bschwartz]

Thanks for the nice words, but I think that we are all learning here in the fórum.

Markmzz

Absolutely. For me, this has been one of the best ways to improve my Excel knowledge.

Many of the tricks I learned are from your posts.