Okay,
So I'm having a hard time coming up with a formula that will actively search through a column and compare multiple values for the same policy holder so that I can determine if a new policy is truly a brand new policy or a transfer of policy number.
I can figure this out with a basic formula - if there the policy holder only had one policy. However, several of the policy holders have several existing policies and I'm at a loss as to a formula that would be able to "search" through their values and return something like "Transfer" in an adjacent column that would allow me to filter them out of a "New Policies" report.
I've attached a sample file that shows my problem. The way to determine if it is a transfer is that the policy holder will have a new line item with a "Start Date" that is the same as on of their other policies "Cancellation Date". The policy holder "Tom" represents my exact dilemma - multiple policies with one obvious transfer, I just need a formula that can be applied to thousands and thousands of rows that I can then sort and eliminate the transfers.
If anyone could help me with this, it would be greatly appreciated. I'm stuck.
Thanks!
Bookmarks