I have the following sheet (Sheet 1)
https://docs.google.com/spreadsheet/ccc?key=0Ag6TBs80YtTNdHBOUDROYnlWc3lVaWhnM2FzRlZPUlE
where the unique ID is Member ID which is unique for a family rather than an individual
so the task is to have record per unique id. The new sheet would look like sheet 2 in the same sample sheet
The tricky parts to look for are
. Not all members have families
. The primary person i.e. member will have Primary listed in the first column (sheet 1)
. All families are not list in row sequence so the macro will have to go through the whole column to find the full family
. Once the families are found (and put into an array of some sort), they have to be listed chronologically by their date of births.
. Sheet 2 column C Coverage has to coded by linking to Sheet 1 Column Y Product Benefit as follows
individual = IN
member plus spouse = IS
member plus children = IC
family = FA
. If the total number of dependents is more than 6 then skip from 7th dependent onwards (by date of birth order)
I know its a tall order but your help is appriciated in advance.
https://docs.google.com/spreadsheet/ccc?key=0Ag6TBs80YtTNdHBOUDROYnlWc3lVaWhnM2FzRlZPUlE
where the unique ID is Member ID which is unique for a family rather than an individual
so the task is to have record per unique id. The new sheet would look like sheet 2 in the same sample sheet
The tricky parts to look for are
. Not all members have families
. The primary person i.e. member will have Primary listed in the first column (sheet 1)
. All families are not list in row sequence so the macro will have to go through the whole column to find the full family
. Once the families are found (and put into an array of some sort), they have to be listed chronologically by their date of births.
. Sheet 2 column C Coverage has to coded by linking to Sheet 1 Column Y Product Benefit as follows
individual = IN
member plus spouse = IS
member plus children = IC
family = FA
. If the total number of dependents is more than 6 then skip from 7th dependent onwards (by date of birth order)
I know its a tall order but your help is appriciated in advance.
