Formula for searching and identifying

Hello,

I was wondering if someone could kindly assist me.

I've included a mock-up data, which replicates the original data. It includes sample data and sample outcome. I've also included a summary in the EXCEL file of what it is exactly I am trying to do, and which columns are applicable.

Any help would be greatly appreciated.

For any clarifications, concerns, questions, please do not hesitate to ask.

Thanking you in advance for your help.

Hello again,

Further to the sample I've enclosed, I'm wondering if it might be a good idea to insert a new column between EN and EO.

This column can actually look at each condition within each case, find the appropriate major and minors for that case, concatenate them and return the results in the new column. Then I can use the following formula for the remaining columns and drag across:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(EP1,$EO$2:$EO$2)))>0,"YES","NO")

Any suggestions at all would be appreciated.

Thank you.

Hi Ford,

Thank you very much for your reply.

I just tried out the formula for case 1. As you said, YES appears for columns EO, EP, and ES, but not for ER. The reason there is a YES for ER is because condition ACET*COD for case 1 also uses gene 4. I'm not sure if it has anything to do with the fact that that particular condition has an *?

Thank you.

I'm not sure if I create a separate thread for this, but it's in relation to the same sample.

So, I've been trying a different approach, in which I inserted a new column between EN and EO. The new column EO was created with the intention of creating a space to concatenate all the appropriate tests per case.

So, for example, case 1 from the sample shows DEX, ACET*COD, CYM. Based on the data in EH-EK, it should return gene 1, gene 2, gene 4, gene 5. Once the information is available in the newly created column EO, columns EP to EX will return a YES where appropriate (in this case, at 1, 2, 4, and 5).

In order to achieve this, I've tried the following formulas in EO:

=SUBSTITUTE(MCONCAT(IF(ISNUMBER(FIND(","&$EH$2:$EH$10&",",","&EN2&",")),","&$EI$2:$EI$10,"")),",",1) with Ctrl+Shift+Enter and


=VLOOKUP(TRIM(LEFT(SUBSTITUTE(EN2,",",REPT(" ",20)),20)),$EH$2:$EI$10,2,FALSE)&","&VLOOKUP(TRIM(MID(SUBSTITUTE(EN2,",",REPT(" ",20)),20,20)),$EH$2:$EI$10,2,FALSE)&","&VLOOKUP(TRIM(RIGHT(SUBSTITUTE(EN2,",",REPT(" ",20)),20)),$EH$2:$EI$10,2,FALSE)

Following this, I then use the formula:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(EP1,$EO$2:$EO$2)))>0,"YES","NO")

Then drag across in order to get the YES/NO response in columns EP to EX.

For the column EO, out of the two formulas, I get nothing for the first formula. Second formula works, but I noticed the information is not complete (for example, if I were to test it out by adding an additional condition to case 1, it will replace one of the genes with the new condition and always only returns five values).

If insight can be provided either into:

a) A formula that can do this in one hit, similar to Ford's; OR
b) A two part formula, as I've shown above.

Either is fine with me, or any other suggestions you have.

Thank you.

EDIT: When I've tried the VLOOKUP formula in the actual dataset, I do get a #N/A, most likely deriving from the fact that there may be extraneous information in the condition name (e.g. like a number).

Hi Ford,

Ah, no worries, I think there is a few gaps in the sample I provided.

For case 1:

DEX uses 1, 2, and 5.
ACET*COD uses 4 and 7.
CYM uses 1, 2, and 3.

So, for case 1, I would expect the following outcome:

1 2 3 4 5 6 7 8 9
YES YES YES YES YES NO YES NO NO (oops, noticed I missed a couple of minors in the sample data - my apologies).

I hope I've made sense.

Its hard without helper column, you can hide it if you like, not elegant ways but hope it meet your goal

Azumi

Hi Azumi,

Thank you very much for sending this through.

This works perfectly, and I can see what you did there with the helper column. The only problem though is some cases can have up to 50 conditions.

I tried applying your formula to the original dataset, but with up to 50 conditions it got a little messy.

Is there anything else you can suggest?

Thank you.

You right, the formula not longer apropriate for your situation, maybe you try VBA section in http://www.excelforum.com/excel-programming-vba-macros/
VBA is more powerful and wider flexibility for complex situation

Regards
Azumi