Results 1 to 4 of 4

Thread: Issue with a list of data

  1. #1

    Issue with a list of data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 .
    Attached Files Attached Files

  2. #2
    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

  3. #3
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    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?

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,844
    Articles
    0
    Excel Version
    365
    Thread has moved on to http://www.excelguru.ca/forums/showt...h-lots-of-Data
    This one's being closed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •