Attached is a sample Notification Mail Merge spreadsheet that can be used as a template for future use. 
  • Clone this template rather than creating a new spreadsheet each time. This will retain the 'Main' tab that has all formulas required to grab data from the different tabs.
  • Do not rename tabs.

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. 
  • This should cover columns A - X.
  • Columns Y - AC will contain the required formulas so careful not to overwrite them:

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.  
  • Then create three (3) new columns next to it for Agency Liaison 2, Aux Mentor 1 and Aux Mentor 2. Columns J-M:

Agency Liaison 1Agency Liaison 2Aux Mentor 1Aux 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. 
    • Ex. Christina Barstow has two aux mentors. So one of them should be in aux mentor 1 and the other in aux mentor 2. Move over the person’s name and then delete the row that you moved them from. So in the end Christina Barstow will look like this:

First NameLast NameHost AgencyTypeRoleAgency Liaison 1Agency liaison 2Aux Mentor 1Aux Mentor 2
RaulBrens JrU.S. Department of State1st yearAuxiliary MentorMatt Chessen
Anthony GreskoJames 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 1Society Name 1Grant Contact 2Society 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.