Family Code, conditional automatic numbering

[abshaw]

I have the following excel sheet From column A through F. Based on the data provided i need to create a Person code with the following Logic conditions.

Person Code = Member ID + Individual Code
Individual Code is determined as follows

01=Primary; 02=Spouse; 03=Oldest Dependent; 04=Next Oldest Dependent

Condition:

02 code only belongs to a spouse, therefore in case of the absence of a spouse the first kid still get the code 03.


Your help is appreciated, thanks

Note: I would prefer to not use macros, any inline codes would help immensely.

 

[abshaw]

I have the following excel sheet From column A through F. Based on the data provided i need to create a Person code with the following Logic conditions.

Person Code = Member ID + Individual Code
Individual Code is determined as follows

01=Primary; 02=Spouse; 03=Oldest Dependent; 04=Next Oldest Dependent

Condition:

02 code only belongs to a spouse, therefore in case of the absence of a spouse the first kid still get the code 03.



Record Type--------Member ID----First Name----Last Name-----DOB----------Relationship------Person Code------------Logic Help
PRIMARY-----------949011439----Jam-----------Bro----------8/15/1983------Primary----------94901143901-----------New Family
PRIMARY-----------949011458----Del------------Pro----------5/19/1954-----Primary----------94901145801-----------New Family
PRIMARY-----------949011459----Wal----------Who----------5/27/1970-----Primary----------94901145901-----------New Family
DEPENDENT--------949011459----Eil------------Who----------9/27/1970-----Spouse----------94901145902-----------Loop
DEPENDENT--------949011459----Mel-----------Who----------10/14/1994----Daughter--------94901145903-----------Loop
DEPENDENT--------949011459----Hea-----------Who----------10/24/1995----Daughter-------94901145904-----------Loop
DEPENDENT--------949011459----And-----------Who----------8/25/2005-----Son------------94901145905-----------Loop
PRIMARY-----------949011468----Ann----------Gold----------12/26/1969-----Primary--------94901146801-----------New Family
DEPENDENT--------949011468----Tho----------Gold----------12/27/2006-----Son------------94901146803-----------Loop
PRIMARY-----------949011489----Cla----------Cua----------12/12/1976------Primary--------94901148901-----------New Family
PRIMARY-----------949011530----bre----------duby---------1/22/1990-------Primary--------94901153001-----------New Family
PRIMARY-----------949011552----Jur----------Bal----------10/27/1973-------Primary--------94901155201-----------New Family
DEPENDENT--------949011552----Jur----------Bal----------6/4/2004----------Son------------94901155203-----------Loop
DEPENDENT--------949011552----Jer----------Bal----------7/27/2007---------Son------------94901155204-----------Loop



another thing to keep in mind is that the data will not always be sorted by the date of births (DOB) and the primary can sometimes come after its dependent.






Your help is appreciated, thanks

Note: I would prefer to not use macros, any inline codes would help immensely.

 

[abshaw]

Hmm No takers on this one