A Mail Merge with Duplicate and Incomplete Records
You have 90 minutes to produce 120 appointment letters for a unit. The template in MS Word is 180 words long. The data source is an Excel sheet with 120 rows, but it contains 7 duplicate service numbers, 3 rows with no rank, dates entered in three formats (12/03/2024, 2024-03-12 and 12 Mar 24), and one name beginning with an apostrophe that disappears in the merge. A trial merge produces exactly 120 letters. A colleague says: “Print all 120 and remove the duplicates by hand; nobody will notice.” Your typing speed is 38 words per minute with 97 per cent accuracy. The officer wants one letter per person, correct ranks, and a printed date line in a single format. The letters must be signed before 1500.
1. The trial merge produces exactly 120 letters. Explain why this does not show that the data is clean, and list the first three checks you would run.
Read the worked answer
A merge returns one letter per row, not one letter per person. If the sheet has 120 rows and 7 duplicate service numbers, 120 letters can still contain duplicate people. The count only proves that Word read 120 records; it says nothing about uniqueness, completeness or formatting. The first three checks are:
- Unique service numbers: use a helper column such as
=COUNTIF(A:A,A2)and filter for counts greater than 1. Seven duplicates should produce seven or more flagged rows, depending on how many copies exist. - Required fields: filter for blank rank, blank name and blank date. The three missing ranks must be traced to the source record, not guessed.
- Date consistency: inspect the date column’s underlying values, not just the display. Text such as “12 Mar 24” may merge differently from a real Excel date; convert all dates to one true date format before merging. A fourth check would be the apostrophe name, which may be stored as text and lost when the merge field is applied.
2. State the rule you would apply to the seven duplicate service numbers and the three missing ranks. What must not be done before the merge?
Read the worked answer
For duplicates, the rule should be: one letter per unique service number, using the most current verified record. Sort or filter by service number, compare the duplicate rows, and keep the row that matches the unit’s current nominal roll; if two rows differ, the orderly room must confirm which is correct. Do not simply delete the second row because it appears later in the sheet. For missing ranks, the rule should be: no merge until the rank is verified from the nominal roll or the officer’s record. A guessed rank on an appointment letter is a serious error, and a blank rank looks careless. Mark the three rows in a separate “query” column and resolve them before merging. What must not be done: do not print first and correct afterwards; do not delete rows without a record of what was removed; do not overwrite the original sheet (work on a copy); and do not let Excel “auto-correct” the apostrophe name without checking the displayed result.
3. Estimate the time needed to type the 120 letters manually at 38 words per minute with 97 per cent accuracy. Use the estimate to decide whether the colleague’s print-then-fix plan is acceptable.
Read the worked answer
Each letter is 180 words, so 120 × 180 = 21,600 words. At 38 words per minute, 21,600 ÷ 38 = 568.4 minutes, or about 9.5 hours of pure typing. At 97 per cent accuracy, 3 per cent of 21,600 = 648 word-level errors to find and correct; even at six corrections per minute that is another 108 minutes, and realistic proofreading is slower. The available window is 90 minutes, so manual typing is impossible. The print-then-fix plan is also unacceptable as a control. Printing 120 letters first wastes paper and toner, and duplicate letters or missing ranks are exactly the errors that a signature check will catch. The correct judgement is to clean the data, run the merge once, and spot-check a sample of letters before printing the full run.