Array, Loop, Auto-numbering with a twist Issue

[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.

 

[jimrward]

i have had a go as follows using formula as follows,

in column I, added the following

=IF(AND(F2<>"Spouse",F2<> "Primary"),B2,"")

this will only add in the memberid when it is a sprog

in column J added something similar to get the DOB if they are a sprog

=IF(AND(F2<>"Spouse",F2<> "Primary"),E2,"")

in column K I have added the following to rank the DOB's, RANK does not work on dates so using SUMPRODUCT instead

=IF(J2<>"",SUMPRODUCT((I2=$I$2:$I$15)*(J2>$J$2:$J$15))+3,"")

note I have used the range up to row 15, you will need to adjust accordingly and drag the formulas down thru your range

the number in column K can be added to the memberid using the

=IF(F2="Primary",B2&"01",IF(F2="Spouse",B2&"02",B2&TEXT(K2,"00"))))

 

[abshaw]

Jim

thank you so much, this is working perfectly, you are my hero...

 

[jimrward]

how does it run with unsorted data, should hopefully be ok, providing you cover the whole range from row 2 to end of data

I was using the SUMPRODUCT method for ranking runners in a race and thought ah ha with a bit of lateral thinking here

It might be possible to do in one big formula, but I like to keep things simple if only for my little mind

 

[abshaw]

I havn't even taken the chance, i am taking one extra step to sort and then bring in the data every week. Will create a dummy file to test the unsorted data over the weekend.