Macro to determine values

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.