compare dates using 2 conditions

Appreciate if someone could help me here.
I need to do a test against an ID to see if a date is greater than another date based on a 'type' indicator.

For example:

ID Date Type
1 31/01/2011 I
1 31/01/2011 A
1 31/12/2011 A
1 31/12/2011 C

The test is: If date of ID 1 Type C is > date of ID 1 of Type I return a yes, otherwise return no. In this case the answer would be Yes.

Example workbook attached. I have to do this with 1000 different records hence the need for a formula approach, which I don't how to do.

Thanks

attatch file may be helpful

Try this array formula in D2

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter.
Drag/Fill Down.

Note
You have blanks in Column C, I have assumed that they should be read as "I"

My apology, forgot to finish the formula, missed the INDEX bit ...

Try this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter.
Drag/Fill Down.

[EDIT]
You might need to use >= rather than > for when the dates are equal.

I must be missing something, but it looks to me that it returns "No", as it should, in your last workbook ...

Do you have calculation set to Automatic?

Are you sure your calculation options are set to Automatic?

All is okay with my copy of your file

[EDIT]
It's the blanks in the Types that are causing the problem when sorting, are there blanks in your actual sheet?

Try this formula, it should handle the sorting with blanks in Types.

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter.
Drag/Fill Down.

Have a look at this workbook

Hi Marcol,
Thanks for your work so far!
I downloaded your test workbook, which looked fine when I first opended it, but when I changed C2 from 'A' to 'I' I get #NUM! in your array formulas - there is nothing I can do to make the #NUM! disappear except open and close the workbook.
Also, I changed K5 from 'I' to 'A' and K2 from 'A' to 'I' and I get #N/A at L4.
Finally, I tried my original workbook on my personal laptop last night at home and I still get the same errors reported yesterday - my personal laptop also runs Excel 2003 but on Win 7?

New screenshot attached