Macro to determine values

[treeleaf20]

I'm using the following formula:
=IF(ISNUMBER(MATCH(1,IF($A$2:$A$500=A322,IF($L$2:$L$500="Failed",1)),0)),"Failed",IF(ISNUMBER(MATCH(1,IF($A$2:$A$500=A322,IF($L$2:$L$500="Not Completed",1)),0)),"Not Completed","Passed"))

However, I want to incorporate a couple more things into the formula and can't figure it out. I want the following to happen:
1) If anything is Failed the result should be Failed
2) If everything is Passed the result should be Passed
3) If it's No Run and Passed the result should be Not Completed
4) If everything is No Run the result should be No Run

Those are the scenarios that the formula should cover. Any help is greatly appreciated.

Thanks in advance.

[rylo]

Hi

1) Can you please attach an example file
2) Is there a set number of items, or something in the range that will give you a count to match.
3) Where is this formula, and is it array entered.
4) Where do you want the result?

As an example for point 1, you could do something like

=if(countif(L2:L500,"Failed")>0,"Failed","")


rylo

[treeleaf20]

1) I attached a sample file
2) No, there is no set items in the range. As you can see in the example I provided the test ID varies on how many times it appears
3) In my sheet the formula is entered in column C and it is entered as an array. I put in column D what the desired result should be
4) I'd like the result in column E

Thanks for your continued help.

[treeleaf20]

I tried to update my query to this:
=IF(ISNUMBER(MATCH(1,IF($A$2:$A$498=A30,IF($B$2:$B$498="Failed",1)),0)),"Failed",OR(IF(ISNUMBER(MATCH(1,IF($A$2:$A$498=A30,IF($B$2:$B$498="Not Completed",1)),0)),"Not Completed","Passed"),IF(ISNUMBER(MATCH(1,IF($A$2:$A$498=A30,IF($B$2:$B$498="No Run",1)),0)),"No Run")))

This still works for Failed, however it gives me a #VALUE error for any other value. Any ideas?

Thanks!

[rylo]

Hi

Using your example file, select Data then

1) Insert Name Define, Name = Col_A, Refers To: =OFFSET(Data!$A$2,0,0,COUNT(Data!$A:$A),1)
2) Insert Name Define, Name = Col_B, Refers To: =OFFSET(Col_A,0,1)
3) E2:

=IF(SUMPRODUCT(--(Col_A=A2),--(Col_B="Failed")),"Failed",IF(SUMPRODUCT(--(Col_A=A2),--(Col_B="Passed"))=COUNTIF(Col_A,A2),"Passed",IF(SUMPRODUCT(--(Col_A=A2),--(Col_B="No Run"))=COUNTIF(Col_A,A2),"No Run",IF(SUMPRODUCT((Col_A=A2)*((Col_B="No Run")+(Col_B="Passed")))=COUNTIF(Col_A,A2),"Not Completed",IF(SUMPRODUCT(--(Col_A=A2),--(Col_B="Not Completed"))=COUNTIF(Col_A,A2),"Not Completed","Not Covered")))))

Copy that formula down. It matches your output except for D15 which you have passed, but I think should be No Run.

HTH

rylo

[treeleaf20]

That works amazingly. Thanks for the help.