Multiple logic test formulae

Hi All,

I’m new to the forum but just wondered if someone may be able to suggest a solution to my problem.

I have created an Excel Dashboard to report on a number of KPI’s which are extracted from a CRM database via a report which is then exported to an excel sheet.

The KPI which is reported on is For each Opportunity, did we agree an NDA with a Freelancer prior to presenting the profile to the customer.

The CRM Database has an Opportunity Table which lists the Opportunity Name and an Activity Table which list types of Activities (NDA Agreed, Profile Sent) and date they are carried out on (End Date)

The data I get from the report therefore has 3 columns:

A B C
Opportunity Name Activity_Type Activity End_Date

I am trying to construct a formulae which will allow me to check if the End Date of the Activity Type NDA sent is before the End Date of the Activity Type Profile sent. That is fairly easy but my problem is how do I get the formulae to also check that the two activity types are from the same Opportunity Name since there will be multiple opportunities names, some with NDA sent only (where the Profile hasn’t been sent yet) and in theory some with Profile sent only (if the process hasn’t been followed correctly). In addition I may need to add an additional column since each Opportunity Name can have multiple Contractors and therefore multiple instances of NDA sent and Profile sent and I need to match the test to the same record.

Hope some of this makes sense, I'm having brain failure just writing about it.

Any suggestions or advice much appreciated.

Thanks
Jaime