Array FunctionsIntermediate

Build a Walk-Order Pick List with SORT and FILTER

A picker walks the aisles in bin order, not in the order the orders came in. FILTER pulls out only the lines released for picking, and wrapping it in SORT puts them in the order the shelves actually run, so nobody doubles back from aisle C to aisle A.

Task:

You run the morning shift in a fulfilment warehouse, and the picker needs a list for the first wave. The sheet in A2:D10 has one line per item, with its bin, quantity and status, but only the lines marked Released are cleared to pick. In F2, write a single formula that lists the Bin, Item and Qty of every Released line, sorted by bin so the picker can walk the aisles from A to C without turning back.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve 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.

10 rows × 8 columns18 cells you fill in
ABCDEFGH
1BinItemQtyStatusBinItemQty
2C-14Packing tape 48mm6Released
3A-03Bubble wrap roll2Released
4B-07Shipping labels 4x64On hold
5A-11Cardboard box medium25Released
6C-02Stretch film3Picked
7B-01Void fill paper1Released
8A-05Cardboard box small40On hold
9B-09Mailing bags large50Released
10C-08Fragile stickers10Released
What this exercise teachesMay contain the answer

A pick list is only as good as the route it gives, and the sheet lists lines in the order orders arrived, which jumps from C-14 to A-03 and back out to C-08. FILTER(A2:C10, D2:D10="Released") does the first job: it returns the bin, item and quantity of the six lines cleared to pick, as one block that spills from F2. The test has to name Released rather than rule out On hold, because the sheet has a third status: the stretch film in C-02 is already Picked, and D2:D10<>"On hold" would send someone to fetch it twice. Left there, the list still starts at C-14, so the picker crosses the whole warehouse and comes back. SORT fixes that without any extra arguments, because by default it sorts on the first column of what it is given, and the bin is the first column FILTER returns. The order of the wrapping matters: SORT around FILTER sorts only the six lines that survived, rather than the whole sheet. And because the bins are zero-padded (A-03, not A-3), sorting them as text runs A-03, A-11, B-01 in the same order as the shelves. Because the list is a formula, a line released at 9am joins the route in its right place the moment its status changes.