I85PREP
⌘ K
Civil Service and Municipal Service Computer Testing study materials
Civil Service and Municipal Service Computer Testing study resources

The Rent Statement Mail Merge

Civil Service and Municipal Service Computer Testing · Put your knowledge to work in context.

All case studies

The Rent Statement Mail Merge

You are preparing 950 printed rent statements for the tenants' committee, using a mail merge from a spreadsheet of 1,240 tenancy records held on the shared council drive. Three problems surface. Thirty-eight rows share a tenancy ID with another row, but the names on the paired rows differ. The postcode column loses leading zeros - a code entered as 0X11 4AB displays as X11 4AB - and sorting has scrambled it further. A lookup column returns #N/A for 61 rows, which you trace to trailing spaces pasted in from an old export. Meanwhile a colleague has saved a second copy, 'Final_v3_JD_edit.xlsx', after editing the same workbook, and the committee papers are due at 09:00 tomorrow. Forty-two tenants have no email address on file, and 15 records show a tenancy end date before today but still appear active. The council's data retention rule requires duplicate tenant records to be investigated, not deleted, because each may hold a separate legal interest in the property.

1. Diagnose the #N/A errors and the postcode problem, and set out the fix for each with a verification step.

Read the worked answer

#N/A - cause: the lookup is doing an exact match, and trailing (or non-breaking) spaces from the old export mean 'T1042 ' does not equal 'T1042'. A value that looks identical on screen is not equal as text.

Fix: add a helper column, e.g. =TRIM(SUBSTITUTE(B2,CHAR(160)," ")), and look up against the cleaned key, or use XLOOKUP with the trimmed key on both sides. Keep the original column intact so the change is auditable.

Verify: count the affected IDs against the source list with COUNTIF; all 61 should return a match, and 5 randomly chosen records should be opened and eyeballed against the housing system.

Postcode - cause: the column is formatted as Number or General, so the leading zero is dropped. If the value was stored as a number, the zero is already gone from the file and cannot be recovered by reformatting.

Fix: if the zeros are intact, pre-format the column as Text, then re-import via Data > Text to Columns specifying Text. If the zeros are lost, do not guess them - re-export from the housing system with the postcode field as text and re-import, then reconcile by tenancy ID.

Verify: every postcode should match the standard outward-code pattern, and the count of rows should still be 1,240.

2. How would you handle the 38 duplicate rows before the merge, given the retention rule?

Read the worked answer

Do not delete anything. The rule exists because a duplicate row may be a joint tenancy, a succession, or a re-let - three different legal situations that look identical in a spreadsheet.

Process:

  1. Copy the master to a working file; make the original read-only.
  2. Sort and filter by tenancy ID to group the 38 rows into their pairs or groups (38 rows may be 19 pairs, or fewer groups of three or more - count before assuming).
  3. For each group, compare name, address, tenancy start and end date, and any interest flag, and classify: genuine duplicate from a merge; joint or successive interest; or a reused ID (a data error).
  4. Log each decision on a reconciliation sheet: ID, both names, classification, action, approver.
  5. Refer the joint/succession and reused-ID cases to a housing officer for confirmation. Only genuine duplicates get merged, and only after sign-off.

For the merge itself, do not edit the master. Build a separate filtered 'mailing list' sheet containing only active tenancies with a confirmed single addressee. This also resolves the 15 expired tenancies - mailing a statement to a former tenant is a data-protection issue, so exclude them and follow the forwarding-address procedure.

3. How would you reconcile the conflicting file saved by your colleague, and what would you change to prevent a repeat?

Read the worked answer

Reconcile, don't overwrite. Overwriting either copy destroys work and leaves no audit trail.

  1. Save both files immediately under distinct names; stop editing the shared copy.
  2. Use the drive's version history to establish who changed what and when - that often resolves which edits are already merged.
  3. Compare by tenancy ID rather than by eye: in each file, flag IDs that are new, missing or changed in the key fields (name, address, postcode, end date). A helper column with COUNTIF against the other file's ID list gives the set difference in minutes.
  4. Merge into a new master and log the merge on a change-log sheet: what came from which file, by whom.
  5. Confirm the merged total reconciles to 1,240 unique IDs before running the merge to print.

Prevention: one master file with edit rights limited to two named people and read-only for everyone else; a change-log tab; a naming convention with date and initials (YYMMDD_JD); co-authoring on the shared platform instead of emailing copies; and a rule that no one saves a file called 'Final' until it is actually final. For a 09:00 deadline, the merge should be run from the locked master and a PDF retained as the record of what was issued.