Hi I have a file of 5000 lines where it has forename, surname, address but I need to determine if they took a policy out on a certain date, cancelled it and purchased a different one. is there a formula to help do this? below is a test file of data that i need to do this to. Basically within the list it shows different years of account and I need to determine whether someone has bought 2 policies within the same year and cancelled any of them. I personally cannot see any parameters that would enable this to be done as all references are different so cannot do an If statement to say if this matches. Any help would be much appreciated. File also attached
DOB SURNAME FORENAME RES_PCODE COUNTER PREV_SALES_CHANNEL POLKEY POLREF RCPCODE POL_SDATE NBIND RES_ADDR1 RES_ADDR2 RES_ADDR3 RES_ADDR4 RES_ADDR5 RES_ADDR6 EARLIEST_SDATE ZSUBCHAN_DESC ZBRAND_DESC POLICY_COUNT POL_ADATE POL_QDATE CHANNEL SALES_CHANNEL POL_STATUS2 CANCELLATION_DATE 17-Oct-56 test test testing 1 9.04E+12 49545373 541 01/05/2009 N test test test test test test 01/05/2009 undefined TEST 1 30/04/2009 29/04/2009 W WEB ACCEPTED ONLINE A . 17-Oct-56 test test testing 1 1.00E+13 89645045 568 01/05/2010 N test test test test test test 01/05/2010 Confused TEST 1 26/04/2010 26/04/2010 A AGG ACCEPTED ON PHONE A . 17-Oct-56 test test testing 1 1.00E+13 49545373 541 01/05/2010 R test test test test test test 01/05/2009 undefined TEST 2 30/04/2010 27/03/2010 W WEB ACCEPTED ON PHONE X 08-May-10 17-Oct-56 test test testing 1 1.10E+13 89645045 568 01/05/2011 R test test test test test test 01/05/2010 Confused TEST 2 30/04/2011 27/03/2011 A AGG ACCEPTED ON PHONE A .
With the above I need to know if it is possible to be able to find out the solution with a formula or similar as the address could be different but the names could be different and they need to be treated as seperate items, really stuck on what the next move would be without going line by line checking. Thanks in advance
If the name changes, and the address changes, how would anyone know they were the same people/customer?
Would a search for policies that don't run for a full year help you?
Do you not code policies in your system as REWRITE?
Thread has moved on to http://www.excelguru.ca/forums/showt...h-lots-of-Data
This one's being closed.
Posting Permissions
Bookmarks