Need help to find policy start date by comparing poicy # & transaction date that falls wit

Hi, I need help from all the experts who can help me.
I have a situation wherein 2 lists of data as following:

Sheet 1 - Contains a Unique Policy number list which will have multiple lines repeating but with different transaction dates (which are basically a date a person entered who is maintaing daily enteries for purchases).

Sheet 2- Contains a the same kind of Unique (policy#) number however has Policy Term dates (Every year a new policy is renewed for these unique numbers.

What I need is I need to do is find the Term Start date and Expiration date for each Unique Policy date on Sheet 1 by comparing whether the Transaction date falls in which Term of that Unique Policy from Sheet 2.

Note: A unique policy might have multiple Terms so we will have look first with the Policy# and then try to find whether my Transaction date falls in which Unique Policy numbes term and provide me the Start Date in a cell besides Transaction date and Expiration date of term besides the Start date of Term.

Friends please help me out with this.

Try this Macro

Please Login or Register  to view this content.

Hi,

I tried the formula suggested by you however it seems there is something missing in it. I just blank cell once I have added the formula to the cell. Could you please add the formula on the file and attach it back to this thread.

Thanks a lot for your help.

Alternatively,


=IF(SUM(Database!$C$4:$C$18*(Database!$A$4:$A$18=A4)*(Database!$B$4:$B$18=B4)*(Database!$D$4:$D$18>=C4)*(Database!$C$4:$C$18<C4))=0,"",SUM(Database!$C$4:$C$18*(Database!$A$4:$A$18=A4)*(Database!$B$4:$B$18=B4)*(Database!$D$4:$D$18>=C4)*(Database!$C$4:$C$18<C4)))


=IF(SUM(Database!$D$4:$D$18*(Database!$A$4:$A$18=A4)*(Database!$B$4:$B$18=B4)*(Database!$D$4:$D$18>=C4)*(Database!$C$4:$C$18<C4))=0,"",SUM(Database!$D$4:$D$18*(Database!$A$4:$A$18=A4)*(Database!$B$4:$B$18=B4)*(Database!$D$4:$D$18>=C4)*(Database!$C$4:$C$18<C4)))


you need to confirm these by pressing CTRL+SHIFT+ENTER (array formula)


I have updated the formula in the attached file.

Hi Mehmetcik,

Your Macro worked very well however I need some more help from you in the same sample and macro.
I had a scenario wherein the transaction date for one of the Row 10 on the sample file had date as 07/01/2013 and it has taken Policy Effective date as 07/01/2012 & Policy Expiration date as 07/01/2013. However I want it to find the next policy term i.e. I want the macro to find the next policy term if my Transaction Date and expiration date is same. So I desire to find Policy Effective date: 07/01/2013 & Policy Expiration date: 07/01/2014.

Is this possible.

Thanks

Please Login or Register  to view this content.

Hi, I really appreciate your help with all this super coding but for some reason I am not getting my desired result. What I want is if a policy numbers transactions date is 07/01/2013 and for the same policies if there 3 terms starting from Policy Effective date 07/01/12 to 07/01/13 and Policy effective date 07/01/13 to 07/01/14. Then Macro should pick policy effective date as 07/01/13 and expiration date as 07/01/14.

Hi yes I have tried but I am getting value error

Hi, Thanks for the formula however if you look at the information I have given my thread you will find that I just want the formula to look for the Policy effective dates and expiration date by comparing the policy and transaction date only and not amount or name.
Could you remake this formula as I tried but it does not seem to be working well and also the transaction date should fall in between the policy effective date and expiration date and the last request is if the transaction date is the expiration date of a given policy then please make the formula to pick the next policy effective date and its respective expiration date.

Hi, well the formula you gave is working well with little modifications I did on them by removing the freeze cells.

You really did an awesome formula by pulling the Policy effective date and expiration date with Policy# & Transaction date.

However if you see the results the formula does not pull any policy effective date and expiration date for a policy # and transactions date wherein the transaction date is either the Policy Effective start date or expiration date.

Can you please try to edit the formulation to pull the Term wherein Transaction date and Policy effective date are same.

Below is the editted formula and attached is the same file.

F4 =IF(SUMPRODUCT(--(Database!B:B=B4),--(Database!C:C<C4),--(Database!D:D>C4),(Database!C:C))=0,"",SUMPRODUCT(--(Database!B:B=B4),--(Database!C:C<C4),--(Database!D:D>C4),(Database!C:C)))
E4 =IF(SUMPRODUCT(--(Database!B:B=B4),--(Database!C:C<C4),--(Database!D:D>C4),(Database!D:D))=0,"",SUMPRODUCT(--(Database!B:B=B4),--(Database!C:C<C4),--(Database!D:D>C4),(Database!D:D)))

Hi,

The macro is still not giving the expected policy# and expiration date. Could you try once again.

I need the Policy effective date and expiration date for those policies wherein the transaction date is same as the policy effective date.

Hi

Can you place your required dated in column H.

We will all be able to help you a lot easier.

Hi mehmetcik,

I have placed the required dates on the column H & I for your reference. Attached is the file. Please let me know if I can be of any further assistance to you.

Try This Version:-

Super thanks Sir you made my day. Thanks alot alot alot alot

Hi mehmetcik,

Am really sorry for this additional request to make changes to the macro but I am really stuck with a big mess. I have recently found that the database I have which contains all the policy numbers and there policy effective dates and expiration dates have been updated in a different manner.

My colleagues had combined 2 or more policy numbers and loaded or created one single policy.
For Example I have 3 Policies = 1. ABC123 2. DEF456 3. GHI789 and all these policies had same effective dates & expiration dates
so my colleagues combined these 3 policy numbers and loaded them as ABC123/DEF456/GHI789

However whenever I make my statement with transactions I will always have single policies in statement sheet.

So I would request you to please help me with my this complex request, which is complex for me but I am sure its not that complex for you.

What I want is a macro to select a policy given on Column B of Sheet Statement find it in Column B of Sheet Database, once it finds a match copy paste that policy back to Sheet Statement Column D.

Once this information is pulled then I would request you to please add the code to find the policy effective dates and expirtion date the D column Policy #.


I have uploaded a sample file with the required details and also mentioned what kind of result I wish the macro to pull on the wooksheet.


Please please please please help me.

I don't understand.

The Database is your master document right?

You have the following entries:=
ABC456/DEF789
DEF100,GHI200
XYZ987-XYZ999

Don't you just want there broken out into individual rows?

Your example shows something similar too, in D4 =

Please Login or Register  to view this content.

How is the data being transferred from the Database to the statement?

can you write code wherein it can copy the policy from poicy on statement sheet and then find it in Database sheet column B and then if it finds a policy in database sheet copy that policy and paste it on D column of Statement sheet. I will manage to edit the macro to find the dates with the macro you gave me earlier.

You did not answer my Question.

You have the following entries:=
ABC456/DEF789
DEF100,GHI200
XYZ987-XYZ999

Don't you just want there broken out into individual rows?

I will assume so and do that for you.

Ok we can try what you are saying