Find whether part of a cell matches with another entire and exact cell

[chabuka]

Hi,

firstly thank a lot for reading and hopefully answering my question.
I need to find cells that within their string contain the content of a different cell. I think I'm having trouble because I need to refer back to the cell with the string and I'm failing miserably at it. Here the situation:

Column A has a suggestion of possible "genes" and looks like this:
B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN
P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN

Column B is a list of genes like:
NB5R3
LMNB1
PRDCX
LM

You will find that cells in column A have many suggestions for the right type of gene (the genes are always hidden inbetween "|" and "_" if that helps with the formula) and column B has the genes that I'm interested in. If any one of the genes in B:B is part of a cell in e.g A1, I need to know that. Any form of highlighting (TRUE/FALSE, Yes/No, COUNT or other) would help.
I can easily highlight cells in B that contain anything from A (=COUNTIF(A:A,"*"&B1&"*")), but with limitations and I didn't manage to do it the other way around which is actually more helpful.

I have tried quite some formulas, e.g.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B:B,A1))),"Yes","No")
=IF(SUMPRODUCT(--MATCH(SEARCH(B:B,A1))),"Yes","No")

but they all were error-prone. I think it has got to do with problems arising from short gene names (e.g. LM) that collide with other gene names (e.g. LMNB1).
I need to find a way to refer to finding the exact content of any of the B cells anywhere within a certain cell in e.g. A1 without finding wrong positives.
I hope this explanation was ok. Thanks in advance, I really apreciate your help!
Lina

 

[Michael M]

Maybe a VBA to highlight cells in Col A
Code assumes cols A & B and both start at row 1

Sub MM1()
Dim lr As Long, r As Long, x As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lr
    For x = 1 To 4 'change col B range to suit
        If InStr(Range("A" & r).Value, Range("B" & x).Value) <> 0 Then
            Range("A" & r).Interior.ColorIndex = 3
        End If
    Next x
Next r
End Sub

 

[Yongle]

(This does not answer your question but may be helpful)

Your original formula would not distinguish between NB5R3 and NB5R33 and ZNB5R3 and therefore could result in false positives
=COUNTIF(A:A,"*"&B1&"*")

Try this instead
=COUNTIF(A:A,"*|"&B2&"_*")

 

[sandy666]

maybe something like this

Column1	Column1.1	Yes / No
B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN	NB5R3	Yes
B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN	LMNB1	No
B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN	PRDCX	No
B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN	LM	No
P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN	NB5R3	No
P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN	LMNB1	Yes
P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN	PRDCX	No
P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN	LM	Yes

if you are able to use PowerQuery

 

[Yongle]

Here is one way

In C2 and copy down to get EXACT string only
="|"&B2&"_"

In D2 and copy down
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$6,A2))),"Yes","No")

BUT C2:C6 cannot contain any blank cells
- becomes a problem to vary the number of items
- one way would be to use a bigger range than required and put dummy values in the extra cells
eg to allow for 19 items $C$2:$C$20 and fill $C$7:$C$20 with ZZZZZ (ie something that would not be found)

A more elegant way would be to use a dynamic range named MyRange, with RefersTo formula
=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C$2:$C$20))

and in E2 copied down
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(myRange,A3))),"Yes","No")

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	Possible	Looking for	Exact string
2​	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN	NB5R3	|NB5R3_	Yes	Yes
3​	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN	LM	|LM_	Yes	Yes
4​	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LM_HUMAN	PRDCX	|PRDCX_	Yes	Yes
5​	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LX_HUMAN	LMNB1	|LMNB1_	No	No
6​	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB11_HUMAN	NB5R33	|NB5R33_	No	No
7​	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN|LM_HUMAN			Yes	Yes
8​	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM_HUMAN			Yes	Yes
9​	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM44_HUMAN			No	No

Sheet: Sheet1

 

[chabuka]

Hi all,

many, many thanks for your help!
Yongle, this is exactly what I need. I have tried it and it works a treat. In the past I was trying to split the cells in A into many cells (text to columns) but that was very messy as sometimes there is one gene suggestion and sometimes up to 25. The way how you suggest it with additional columns C and D will be entirely sufficient as my list in B is always the same without any blank cell danger. But I'm sure someone reading this post will need that in the future.
Sandy, Michael, thank you for your suggestions also! But not being familiar with PowerQuery and very rudimental on VBA, I will go the path of least resistance with Yongle's proposed formulas.

I really appreciate you all taking the time to help others. You have made my excel life much easier.

Best regards,
Lina

 

[Yongle]

thanks for the feedback

 

[jtakw]

Hi,

Here's another way, no helper column needed:

Book1
	A	B	C
1	Possible	Looking for
2	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN	NB5R3	Yes
3	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN	LM	Yes
4	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LM_HUMAN	PRDCX	Yes
5	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LX_HUMAN	LMNB1	No
6	P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB11_HUMAN	NB5R33	No
7	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN|LM_HUMAN		Yes
8	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM_HUMAN		Yes
9	B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM44_HUMAN		No
Sheet510

Cell Formulas
Range	Formula
C2	=IF(ISNUMBER(LOOKUP(2,1/SEARCH("|"&B$2:B$6&"_",A2))),"Yes","No")

Formula copied down.

 

[chabuka]

Thank you, jtakw! This one works also very well and returns the same values as Yongles.

 

[jtakw]

You're welcome, welcome to the forum.