Attached is a sample Notification Mail Merge spreadsheet that can be used as a template for future use.
Instructions for updating mail merge spreadsheet:
1. Run each report – part 1, part 2, etc. (located in the Onboarding Reports sub-folder in the Program Team folder.)
2. Copy and paste part 1 report into the 'Main' tab.
3. Copy and paste all other reports into their respective tabs:
4. In 'part2' tab, rename the contact role's 'Full Name' (column J) to Agency Liaison 1.
|Agency Liaison 1||Agency Liaison 2||Aux Mentor 1||Aux Mentor 2|
Sort by column I (Role). Look at anyone with an auxiliary mentor type and move that mentor’s name into column L (auxiliary mentor 1).
Next, sort by column A (Contact Id for Role). Select column F (Last Name) and do conditional formatting for all cells that contain duplicate values. This will highlight anyone who has more than one of an aux mentor or agency liaison. For any last name that comes up highlighted see what they have two of and move the person’s name into one of the new columns. Basically you are making it so each fellow has only one row and all of their relationships are in columns J-M.
|First Name||Last Name||Host Agency||Type||Role||Agency Liaison 1||Agency liaison 2||Aux Mentor 1||Aux Mentor 2|
|Raul||Brens Jr||U.S. Department of State||1st year||Auxiliary Mentor||Matt Chessen||Anthony Gresko||James Warden|
5. Tab 'part5' (if used in notification letters) will require similar cleaning up since fellows may have more than one person attached to their grant, and each contact shows up on a different row. So you need to create a few new columns and move the extra people to a new column. Columns G through J should be:
|Grant Contact 1||Society Name 1||Grant Contact 2||Society Name 2|
Tip: When you have finished updating that spreadsheet, save the 1st tab as a new spreadsheet for your actual mail merge. Keep the original for your template! This would allow you to delete all the N/A information in the cells without deleting the fancy formulas that are grabbing that information.