Use an array formula to check if a similar contract has ended in the last 12 months

Currently I was just looking for the reporting table I wrote. The logic I wrote doesn't work, so I thought I'd see if I could ask for help here.

Basically, I have a list of contracts, a list of products, a list of end and launch dates, and various other details. I need to know if there is an update product using this information.

Sample data:

Product   Company Number    Start of Contract        End of contract     Contract ID     Include in this month report
Fax            1234             10.09.2013              10.09.2014           1                      No
Fax            1234             10.09.2014              10.09.2015           2                      No     
Box            5678             11.01.2014              30.04.2015           3                      No
Box            5678             01.05.2015              11.01.2016           4                     Yes
Fax            5678             01.05.2015              01.05.2016           5                     Yes
Cup            9876             03.05.2015              03.05.2016           6                     Yes

      

        
        
        
      

    

I want to do this using this data, whether the file is new to a product, new to business, or an update to an existing file.

The logic as to whether or not this month's report should be contracted is simple - just check if the Start date

month requested is elsewhere in the report.

In the above example

• Contract ID 1,3 and 6 are new to business.
• IDs 2 and 4 are updates
• Id 5 is new to the product, with existing business with the company.

I have worked out the main way to develop this month if there is a new product file, because the company number will not be unique, but the product will. I would like to do it better, because if done the way it is, it will return as "false" for the first occurrence of where the product was subsequently updated.

What I need to do is find a way to check if we have a previous contract with a specified product, contracts with brand new customers and contracts that have a previous contract with us that was NOT with the same product. Basically, I need the logic for the answers below!

Results:

Contract ID     Renewal?        New to Product?         New to Business?
    1              No                  No                     Yes
    2             Yes                  No                     No
    3              No                  No                     Yes
    4             Yes                  No                     No
    5              No                 Yes                     No
    6              No                  No                     Yes

      

        
        
        
      

    

I have several helper columns so far, such as "ended in last 12 months" and "number of agreements". but I turned around all the time when it came to the logic for the three final results!