Mar 03 2022 08:35 AM
Looking for a way to put a value into a column if one column is even partially true to another
example
Column A --bobs cars; Column B --bobs trucks
since both have 'bob' in the column want a '1' placed in column C
Is this possible?
Thank you for the help.
Mar 03 2022 08:45 AM
Mar 03 2022 08:48 AM
@AnalystNerd but what if it isn't just "bob' in column A? Basically if any word in column A matches column B want to place the '1' in the 3rd column. Bob was just an example.
I appreciate the help.
Mar 03 2022 08:50 AM
Is it possible? Yes, and No. It all depends on where the matching string of letters is to be found. On how varied your two columns are going to be.
In the example you give, it's quite simple, since the three characters "bob" are the first three characters in each string, for then this formula works
=IF(FIND(LEFT(A1,3),B1),1,0)
But if the "bob" characters are buried elsewhere in the text, it becomes trickier.
And if your real world issue is far more variable, sometimes with 2 characters matching, sometimes with 4 or 5, all the more elusive.
Mar 03 2022 08:52 AM
Mar 03 2022 08:56 AM
Since you're adding complications as we go along, how about giving a much more complete picture here of what you're dealing with. How many words max? What's the bigger picture into which this little task fits?
Mar 03 2022 10:05 AM - edited Mar 03 2022 10:06 AM
You could try this (adjust range references as applicable and assuming you have the let function), but if your text has characters that are 'special' to xml (such as the & character) in column B, then it will not work.
=Let(searchTerms, FILTERXML("<L><I>"&SUBSTITUTE(B1, " ", "</I><I>")&"</I></L>", "//I"),
matches, SUM(--(ISNUMBER(SEARCH(TRANSPOSE(searchTerms), $A$1:$A$10)))),
If(iserror(searchterms), "ERROR", --(matches>0)))