Pull static data from one sheet to use in a VLookup, then output static data + returned values? Can my process be improved?

Hi all,

I have a weekly process at work that I am hoping to improve, and preferably completely automate. With the help of this subreddit I have taken this process from hundreds of steps down to a few dozen, but I think more can be done – I am hoping for suggestions (and if automation seems possible, a point in the right direction as to where to start). I will do my best to describe the process without droning on, but there are a number of nuances which I feel may be important.

Our largest client sends us a weekly census file for their retirees, which contains information (46 columns) that affect the type of materials I am responsible for sending, as well as the content of those materials, per household (retiree + any dependents). Each file can contain between 1 and 30 retirees. To complete my tasks, I use four separate excel files:

• The census file
• The “calculator,” which generates a contribution amount based whether the retiree (and their dependent(s), if applicable) are over or under 65, years of service and what division the employee worked in. It seems like the process is a series of IF formulas, paired with a VLOOKUP.
• A “premium payment tool” which essentially subtracts the contribution from the premium amounts and is formatted for output onto the master mailing list
• A master mailing list, which includes the retiree’s demographic info, plus their coverage options and individualized rates

To say the process is convoluted is an understatement. Each household is one row on the census file – the client provides the ages and disability status of all household members. My current process:

1. Copy/Paste the retiree’s: first and last name, DOB, SSN, address, and phone number. The order on the client’s file does not match the order that my company uses. I typically will copy/paste one column at a time for all rows in the census file, then complete the following steps one row at a time

2. Determine the ages of the retiree, their spouse, and their children (if applicable). If all household members are in the same age group (Pre or Post-65) based on the date of retirement, only one entry on the Mailing file is required. If one household member is in a different age group than the other(s), two entries on the Mailing file are required. Additionally, if a household member has an “E” in the disability column of the file, they are treated as being Post-65 regardless of age.

3. Using the Calculator, determine the employee's monthly contribution amount based on service years, group, and section type. The process is repeated if the employee has a spouse, and again if they have a child dependent (so from 1-3 times per employee).

4. Copy the monthly contribution amount for each household member into the Premium Payment tool – there are two sheets, one for Pre-65 and one for Post-65. Each sheet has cells for the Retiree, Spouse, and Child. The contribution amount is subtracted from the premium rates and formatted for output to the Mailing file. If there is no contribution amount under one household member, the output returns a “N/A” under that coverage. Ex:

https://preview.redd.it/csqgmr9038sg1.jpg?width=975&format=pjpg&auto=webp&s=aa781f5373077cf354886b63ae99349d178281ed

5. Copy the “You Pay” amount onto the Mailing file

Besides the time it takes to complete this process for larger files, the sheer amount of copy/pasting required leaves a lot of room for error. What I am working with now is infinitely better than what the girl before me was doing. I was able to add enough formulas to reduce the copy/pasting by about 75% but I suspect more can be done to automate things – I’m just not sure if it’s feasible solely in Excel. I’d love to hear what the community thinks, please let me know what additional info I can provide. Thank you all so much!