If one dup fails they all fail...

[HankJ]

I have a problem that seems to have beaten me.
I have a number of compounds that are tested at different concentrations and I need, that if one concentration fails then they all do.

[TABLE="width: 402"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Sample
[/TD]
[TD="align: center"]Enzyme
[/TD]
[TD="align: center"]Mean%Act[/TD]
[TD="align: center"]Rep QC[/TD]
[TD="align: center"]Enzyme QC[/TD]
[TD="align: center"]QC Flag[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]FAIL[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]FAIL[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]FAIL[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]FAIL[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-30[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]FAIL[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-30[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]FAIL[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS
[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-30[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-30[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-30[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-1-30[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS
[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS
[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-30[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
[TR]
[TD="align: center"]Cmp-2-30[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[TD="align: center"]PASS[/TD]
[/TR]
</tbody>[/TABLE]

In the example above Cpm 1 and 2 are tested at 10 and 30µM for two enzymes. Cmp 1 fails with enzyme 1 at 10µM.

I've tried countif and sumproduct with a concatenation of the Cmp-1-2 for the cmp number and the kinase number...

I would be grateful if someone could suggest a way failing Cmp 1 in the QC Flag column.

Hankj

 

[AliGW]

Try this in F2 copied down:

=IF(COUNTIFS($D$2:$D$17,"FAIL",$B$2:$B$17,B2,$A$2:$A$17,LEFT(A2,5)&"*")>0,"FAIL","PASS")

 

[HankJ]

Try this in F2 copied down:

=IF(COUNTIFS($D$2:$D$17,"FAIL",$B$2:$B$17,B2,$A$2:$A$17,LEFT(A2,5)&"*")>0,"FAIL","PASS")

Good morning AliGW
Thank you for your reply.
Sorry to say I only have 2003 at home, so getting an error at the moment, will check it when I get to work later today.
Could I ask you what the &"*" does?
Best wishes
Hankj

 

[AliGW]

OK - try this:

=IF(COUNTIFS($D$2:$D$17,"FAIL",$B$2:$B$17,B2,$A$2:$A$17,CONCATENATE(LEFT(A2,5),"*"))>0,"FAIL","PASS")

& is a new alternative to CONCATENATE.

 

[HankJ]

OK - try this:

=IF(COUNTIFS($D$2:$D$17,"FAIL",$B$2:$B$17,B2,$A$2:$A$17,CONCATENATE(LEFT(A2,5),"*"))>0,"FAIL","PASS")

& is a new alternative to CONCATENATE.

Sorry this took so long, issues with logging in.

They both worked a treat, when I got to work.
I understand the concatenate, I was wondering if the "*" is a wild card or some such.

Anyways, many thanks AliGW.
Hankj

 

[AliGW]

Glad to have helped. Yes, the asterisk is a wildcard.