Text FunctionsAdvanced

Build Each Supplier's Reorder Line with TEXTJOIN and FILTER

A purchase email wants one line per supplier, not a filtered table someone has to copy from. FILTER picks out the rows that need ordering and TEXTJOIN folds them into a single comma-separated cell, so the whole reorder list for a supplier comes out of one formula and updates itself when the stock counts change.

Task:

You run supplies for a busy outpatient clinic, and every Thursday you email each supplier what to send. The stock count in A2:D10 lists each item, who supplies it, how many are on hand and its reorder point; an item needs ordering when its on-hand count is at or below its reorder point. In G2, write one formula that lists every item the supplier in F2 needs to send, separated by a comma and a space, and shows "Nothing to order" if there are none. Copy it down to G4.

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 × 7 columns3 cells you fill in
ABCDEFG
1ItemSupplierOn HandReorder PointSupplierReorder Line
2Nitrile gloves (M)Medline4060Medline
3Gauze padsMedline220150Cardinal
4Saline flushCardinal3580Henry Schein
5Alcohol swabsHenry Schein900500
6Exam table paperCardinal1212
7Face masksMedline75100
8Tongue depressorsHenry Schein300200
9Cotton ballsCardinal140100
10BandagesHenry Schein450400
What this exercise teachesMay contain the answer

The supplier needs a sentence, not a table, and the two functions split that job cleanly. FILTER does the choosing: multiplying the two tests turns each row into 1 only where the supplier matches and the count is at or below the reorder point, so for Medline the gloves and the face masks survive and the gauze, comfortably above its 150, does not. TEXTJOIN then does the writing, putting ", " between whatever FILTER hands it, which is why the result is one cell you can paste into an email rather than a spill you have to tidy. The exam table paper is the line worth checking: 12 on hand against a reorder point of 12 is exactly the case the word "at" in the rule covers, and a formula written with < instead of <= drops it, sends Cardinal an order without it and runs the clinic out of paper the following week. Henry Schein shows why FILTER's third argument matters. Every one of its items is above its reorder point, and without a fallback FILTER would return an error, which TEXTJOIN would pass straight through to the cell; with "Nothing to order" as the fallback, that single word becomes the line, and the email says so plainly. Because the formula reads the counts rather than a list you typed, next Thursday's line rewrites itself the moment the new counts go in.