Macro to determine values

**treeleaf20** · 06-09-2009, 08:45 AM

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** · 06-09-2009, 10:19 AM

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** · 06-09-2009, 07:15 PM

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** · 06-09-2009, 07:25 PM

That works amazingly. Thanks for the help.

**treeleaf20** · 06-10-2009, 08:44 AM

My only problem is that I'm trying to record this in a macro, but it keeps giving me an error when I try and record it. Any idea how to do this?

**rylo** · 06-10-2009, 06:09 PM

Hi

Just how much do you need the macro to do?

Create the defined names?
Insert the formula?
Copy the formula down to cover the required range?

Detail just what you want the macro to do.

rylo