Identify Cancelling Transactions Based on Unique ID

[mysticmoron109]

I have no idea how to explain this in excel terms but the desired result it is to capture "New business" only.

In Column A we have Policy Numbers
In Column H we have Transaction Types (New Policy, New, Cancellation, Reinstatement, Renewal, Extension)
In Column EB we have the $ amount of the transaction.

What I am trying to accomplish is to identify New Business only. Unfortunately It's not as simple as doing a sum of "New" and subtracting the sum of "Cancel" because some cancels relate to policies which doesnt have a new/renewal in the same period. I.e, some cancellations are standalone cancellations.

For Example, Let's say you had the following policies and transactions:

1) Policy A New 100
Policy A Cancel 100

The New business here would be 0

2) Policy B New 200

The New Business here would be 0

3) Policy C Cancel 500
No other occurrence of "policy C" found in the data, therefore this should be ignored.

Hopefully that makes sense and I have attached a somewhat representative spreadsheet


Thanks in advance