Find last occurrence of each unique value in column if criteria met

[dazed007]

Hello, firstly this is a follow up question to the below thread:

http://www.mrexcel.com/forum/excel-...nce-each-unique-value-column.html#post4143098

I was kindly give the following array formula to display Yes in column B for the last occurrence of each unique value in column A:

=REPT("Yes",ROWS($1:1)=MATCH(1,0/(A$1:A$9=A1)))

I would now like to achieve the same with but don't want to include rows where column C equals NA.

For example:

A	B	C
BN1
BN2
BN1
BN4	Yes
BN3	Yes
BN3		NA
BN2	Yes
BN1
BN1	Yes

<tbody>
</tbody>

Hope this is explained clearly enough and appreciate any help you can offer.

 

[XOR LX]

Hi again.

Again, array formula**:

=REPT("Yes",ROWS($1:1)=MATCH(1,0/((A$1:A$9=A1)*(C$1:C$9<>"NA"))))


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[dazed007]

Hi! Really glad you were around again to help, I've tested and of course your solution works as requested!

Thank you (again).

 

[XOR LX]

Sure! Glad to help again!

Cheers