A hold backlog of hundreds of rows, all filed under one misleading label, became a few dozen checks with named owners. Roughly half of it cleared in one morning, and the rest went out the same day as one message per owner.
Held records were blocking the pay run. They had piled into the hundreds and the backlog was due by the next morning. Every row sat under the same error label, so it looked like one problem with one owner. It was not. Reading the message text row by row showed several distinct error types, each owned by a different person, and some that only the supervisor could fix.
Classify by the real message. I exported every row with its full message text and sorted the rows into classes by what the message said, not by the label on top. A queue label tells you what the system thinks is wrong. The message tells you what is actually wrong.
Name an owner for every class. Each class got one owner before anything moved: rows I could fix myself, rows for the supervisor (pay types, rates, anything from a prior period) and rows for the account specialist (missing budget lines). A class with no clear owner does not wait in limbo. It gets one assigned first.
Group rows into checks. Inside my own classes I looked for the smallest key where one decision fixes every row that shares it. That key was account plus person plus pay code. Hundreds of rows collapsed into a few dozen checks, about five times fewer decisions.
Stable IDs. The first worksheet looked rows up by their position. A simple re-sort broke every summary formula in it. I rebuilt it with a stable check ID on every row and keyed every lookup on that ID, so sorting and filtering can no longer move an answer.
Prove it with a re-export. After each fix I spot-checked one row in that check. Then I re-exported the hold list and compared it with the original. A check counted as cleared only when its rows were gone from the new export, not when I believed it was done.
Hold Worksheet, Summary tab: by type
| Type | Owner | Rows | Checks |
|---|---|---|---|
| Missing approval | Megan Fairhall | 75 | 14 |
| Duplicate entry | Megan Fairhall | 40 | 8 |
| Rate not on plan | Chris Dunmore | 39 | 8 |
| Expired plan | Rachel Ostley | 37 | 6 |
| Prior period | Sarah Tellwick | 28 | 5 |
| Vendor mismatch | James Welford | 21 | 4 |
| Total | 240 | 45 |
Hold Worksheet, Summary tab: reconciliation
| Check | Value | Result |
|---|---|---|
| Rows in export | 240 | OK |
| Rows covered by checks | 240 | OK |
| Unclassified rows | 0 | OK |
| Checks with zero rows | 0 | OK |
| Checks missing from Checks tab | 0 | OK |
| Owner totals equal type totals | 240 | OK |
Route the rest. Every other owner got one message with only their rows, grouped by check, saying what I saw and what was needed. No forwarded spreadsheet and no reply-all. Two rules held throughout: never edit a source plan to make a record fit, and if a duplicate has no surviving original, escalate it instead of deleting it.
Click any image to see it full size. Every file here is rebuilt with invented data.
The results, in bands. Hundreds of rows became a few dozen decisions, about five times fewer. Roughly half the backlog cleared in one morning and every remaining class had a named owner by the end of that day. A second, smaller hold class later shrank from dozens of rows to a handful of checks the same way. The worksheet became a standing tool, re-run after each re-export.
- My first instinct was to report the backlog as one problem with one owner, because that is what the label said. Reading the messages showed several. Classification now comes before any count or report.
- The first worksheet broke when it was re-sorted, because its lookups used row positions. I rebuilt every lookup on stable check IDs, and no formula depends on row order now.
Download the Hold Worksheet (xlsx)The Backlog GrouperThe Backlog playbook (PDF)Hundreds of exceptions, a few dozen decisionsLive demo of the hold worksheetPart of the work in case study 06
Case study 12