Formula to search for repeat values and return unique information

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!

Thanks FD,

That looks like it does most of what I want... Can you think of a way to get it to return something like "Transfer" in the row of the newly opened policy though? Right now it is returning the policy number of the "new transfer" in the first line item for each policy holder. Without getting into too much detail, I need to the old policy that was cancelled to remain, but I want to be able to to use another formula to either include or exclude the data in the row based on whether or not that row is a new policy that was "Transferred" - because I don't want to count it as new if it is not.

Sky, that works great!

Ugh, okay so one more twist. It appears that some of the policy transfers occur in the same month as the initial policy start.

So, someone opens a brand new policy on 1/2/15 and then transfers the policy on 1/5/15 - in cases like these I actually do not want to identify it as a transfer... is there a way to exclude this using some variation of the formula you provided and the Month() formula?

So, if you look at this new version of the file - Tom opened a new policy on 2/10/15 and then transferred it to a different policy on 2/18/15.

My main goal is to generate a count of "Total Policies" and a count of "New Policies For the Month". So, in most cases transfers are made by policy holders several months or years after they start the policy - since this "Newly Transferred" policy is not actually a new policy but a change in an existing policy - I do not want to count it as a new policy.

However, if a new policy is opened in February (as is the case with Tom) and then transferred in the same month - it is a transfer, but it is also a new policy for the month. The simplest solution would be to have the formula return nothing if this scenario occurs so that it doesn't get excluded with the other transfers. But I'm at a loss as to how to do that.

Does that clarify it at all? Sorry for the confusion.

I want Column E to say "Transfer" if a policy holder in column A has one policy with a cancellation date in column D that matches the start date of a different policy (Column C) for the same policy holder -- unless the policy for the two line items in question (the matching policy cancellation date in column D and the policy start date in column C) occurred in the same month of the same year, in this case I would like it to return nothing ("") instead of "Transfer" (which is what it is returning now).

Thank you for all of your help with this, I really appreciate it.

Sorry Duplicate