Thank you for patiently waiting. Indeed your collection of data is irregular because the Census Data sheet are in columns and the New Aetna are in rows. I believe that you will agree that you cannot easily DRAG the formulas down to automatically copy because of the data inconsistency in terms of layout.
Now, for the Subscriber entries, I added a new column to reflect the "Full Name" to match the info in Census sheet. Also, the big purpose here for this new column is to facilitate the VLOOKUP formula so that you can easily pull out relevant information like Zip, Age, DOB, and Gender.
The VLOOKUP formula will look for the full name, when found, it will pull out those data.
After that, you can them DRAG the formula down to copy it to the other cells. However, the critical part is, you need to DELETE the contents for cell range G:I for Spouses and Children because their corresponding data will be pulled out in another way. This is the reason why I said "you cannot easily drag" formulas to copy them since the records are not continuous.
To summarize, the vlookup formula will only work for Subscribers and I suggest you do this first and then drag the formula down to copy. Then DELETE the cells in the range G:I for Spouses and children.
For Spouses and children, the easiest and fastest way is NOT though functions in a formula since you cannot manipulate those range of data because of inconsistent layout. The best approach is by direct cell reference. This is manual though but you just need to do it once in the New Aetna sheet.
This "cell referencing" method is better than simply copy&paste because when you change the data in Census Data, the referenced data in New Aetna are updated automatically.
For instance, in New Aetna sheet, cell G3 that requires spouse's age, type = to insert a formula, click on the Census Data sheet, then locate and click on Q13 and press Enter. That should give you the content of Q13 in Census Data sheet for the New Aetna sheet.
Since you need the other adjacent data for the spouse like DOB or Gender, you can just drag the fill handle of the cell containing the formula to the right to copy the other contents.
Here is my completed sample file:http://wikisend.com/download/240608/hello_kitty_revision_jess2.xls
Please remember to rate my service positively (3-5 stars/faces)
once you have all the information you need. Tips
are always highly appreciated!
If you have any other questions, please ask me more or reply to me – I’ll be happy to respond.