I85PREP
⌘ K
Computer Specialist in Spreadsheet Level 2 study materials
Computer Specialist in Spreadsheet Level 2 study resources

The Sort That Detached 600 Email Addresses

Computer Specialist in Spreadsheet Level 2 · Put your knowledge to work in context.

All case studies

The Sort That Detached 600 Email Addresses

Vendor_Contacts.xlsx contains 9,412 rows: A ContactID, B LastName, C FirstName, D Company, E Email, F SignupDate. Fourteen rows are visual section headers such as '- West Region -' created by merging A:B and typing the text; they sit inside the data. A colleague selected A2:F9412, chose Data > Sort, key = Company A→Z, and confirmed. Afterwards, more than 600 rows show an email that does not match the person's name, the 14 merged banners are scattered through the list, and 37 SignupDate cells no longer filter correctly because they were typed as text (a leading apostrophe, e.g. '03/14/24). The file was saved and closed, so Undo is gone. A duplicate-email report built with conditional formatting flags 212 matches. The list feeds a mail merge next Monday.

1. Explain the mechanism that detached names from emails, and set out the recovery plan given that Undo is unavailable.

Read the worked answer

Mechanism. Sorting moves rows, not columns - but only the rows inside the block Excel believes it is sorting. When the selected block does not cover every adjacent column, Excel raises the Sort Warning: 'Expand the selection' or 'Continue with the current selection'. Choosing Continue sorts only A:F and leaves anything to the right untouched, so the row-to-row pairing of name and email is destroyed. The merged A:B banners compound it: a merged block is treated as one cell, so Excel moves it as a unit and can shift or refuse neighbouring rows, scattering the 14 headers through the list.

Recovery, no Undo available.

  1. File > Info > Version History on the OneDrive/SharePoint copy; restore the version from before the sort. This is the only true fix and it takes two minutes.
  2. No version history? Pull the last backup or the copy attached to an email. Do not attempt to re-sort back - the original order is information you no longer have.
  3. If neither exists, rebuild Email by matching ContactID against the mail system's export with =XLOOKUP(A2,MailExport!A:A,MailExport!E:E,"MISSING").
  4. Verify before the merge: total rows still 9,412, no duplicate ContactIDs, and a manual spot-check of 20 randomly chosen records against a known-good source. Never run the merge on an unverified rebuild.

2. Write the correct cleanup sequence for this file, in order, and say which step makes the failure impossible to repeat.

Read the worked answer

Order matters; do destructive steps last and work on a copy.

  1. Unmerge. Ctrl+A, then Home > Merge & Center to switch merging off. Text stays in the top-left cell of each former merge.
  2. Get the banners out of the data. Either delete them and add a real Region column, or copy their text down a new Region column, filling between them with Go To Special > Blanks, typing = and pressing ↑, then Ctrl+Enter. Banners living inside rows are the single most common cause of broken sorts, broken pivots and broken filters.
  3. Convert to a Table (Ctrl+T), named VendorContacts. This is the step that makes the failure impossible: sorting from a table's header dropdown always moves entire rows, and AdjacentColumns are never left behind.
  4. Sort by Company using that dropdown, never by selecting a subset of cells. The Sort Warning is the signal you have done something wrong - cancel, do not choose Continue.
  5. Fix the 37 text dates before anything else consumes them. Select F, Text to Columns > Delimited > Date MDY, or use a helper =DATEVALUE(TRIM(F2)). Check for #VALUE, paste values over the original, format as Date. Text dates sort and filter as strings, which is why the filter misbehaves.
  6. Deduplicate last, once rows and types are clean.

3. The 212 duplicate emails need resolving. How do you choose which record survives, and what do you check before deleting anything?

Read the worked answer

Remove Duplicates keeps the first row it encounters and deletes the rest, so sort order silently decides which record survives. Decide the rule explicitly, in writing, before running it.

If the rule is 'keep the most recent signup', sort by SignupDate descending so the newest is encountered first (ascending would keep the oldest). Add Email as a secondary sort key to make the outcome deterministic and auditable.

Before deleting:

  • Copy the sheet. The command cannot be undone and there is no preview of what will go.
  • Normalise for inspection: =LOWER(TRIM(E2)) in a helper column. Conditional-formatting duplicates treat '[email protected] ' and '[email protected]' as different, so a naive dedupe leaves real duplicates behind. Dedupe on the normalised column and keep the original casing in another column for display.
  • Eyeball a sample of the flagged pairs. Some 'duplicates' are two genuine contacts at one company sharing a role mailbox; deleting one loses a person.

After deleting: confirm the row count falls by roughly 212, confirm no Company has lost every contact, and confirm SignupDate still carries the values you sorted on. Then point the mail merge at the table and spot-check 20 addresses.