Queue a maintenance team's repair requests by priority without touching the log
SORTBY can carry two columns along for a sort keyed on a third, so the call log it reads from never has to move.
Task:
You coordinate the on-call maintenance team for a 40-unit apartment building. Requests get logged in the order residents call them in, which is not the order the tech should work them — a leaking faucet phoned in first thing shouldn't sit ahead of a gas smell reported an hour later. Each request carries a priority: 1 for an emergency, 2 for urgent, 3 for routine. The office needs the call log left exactly as it came in, since that's the record they keep, and a separate work order built from it. In F2, use SORTBY to list the unit and issue from A2:B7, ordered by the priority in C2:C7, most urgent first. The queue should spill from F2 down to F7.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Unit | Issue | Priority | Unit | Issue | ||
| 2 | 4B | No hot water | 1 | ||||
| 3 | 7C | Leaking faucet | 3 | ||||
| 4 | 2A | Broken thermostat | 2 | ||||
| 5 | 9F | Gas smell reported | 1 | ||||
| 6 | 5D | Squeaky door | 3 | ||||
| 7 | 6E | AC not cooling | 2 |
What this exercise teachesMay contain the answer
SORTBY keeps the call log and the work order as two separate things: A2:C7 never changes, and the formula in F2 produces a second view of it ranked by urgency. Because the sort is stable, 4B and 9F — both priority 1 — come out in the order they were logged rather than swapping places, so two emergencies phoned in minutes apart don't get reshuffled just because one happened to be tied with the other.