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
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