The orders nobody collects: a sheet that flags them by itself, without sending names anywhere

Reading time: about 11 minutes

The situation. Behind the counter there is a shelf of customer orders: bags with a slip stapled on, name and phone number. Some have been there three weeks. Nobody knows which until someone goes through them one by one, and when they do, there are products that should already have gone back to the supplier and people nobody phoned.

It is a good first automation project because the problem is small, well defined and you can see straight away whether it works. But it is also a case where the temptation points the wrong way: "have the AI read the orders and send each customer a WhatsApp". That mixes health data about identified people, an outside service and an automatic send with nobody watching. The good solution is far more modest: a spreadsheet with formulas that sorts and flags, written with AI’s help, that runs on your computer and sends nothing anywhere. The reminder to the customer is decided and made by a person.

What you need to hand: The sheet or notebook where you record orders (it does not go into the chat: you work with an invented copy that has the same columns), any spreadsheet program, and the case on an expiry sheet that checks itself, which uses the same mechanism on a similar problem.

Step by step

Decide what information each order needs

Before any formula, look at how you record orders today. Usually what is missing is exactly what automation needs: the date the product arrived at the pharmacy, which is when the waiting starts. Without that date there is nothing to calculate.

The minimum columns are five: an order number, the arrival date, the product, a contact phone number and the status (waiting, notified, collected, returned). The customer’s name can be there, but nothing that follows needs it: the order number, also written on the bag, identifies the bag without the sheet carrying names.

And one column almost nobody includes that changes everything: the date notified. Without it you cannot tell whether "three weeks" means "three weeks without anyone calling them" or "we called them two weeks ago and they have not come in".

Make an invented copy to work on with AI

The real sheet has people’s phone numbers and products that say a great deal about their health. It does not get pasted into any chat. To write the formulas, the model does not need your data: it needs the structure. So you make a copy with the same columns and ten invented rows — "Order 101, 03/03, product A, 600 000 000, waiting".

Put the awkward cases into the invented rows on purpose: a missing date, an order already notified, one collected, one from two months ago and one from today. That way you check that the formula works with what really happens on your shelf, not just with the easy case.

Look at how you write dates. If some are "03/03/2026", others "3-3" and others "3 March", no formula will work until you make them consistent. It is the number one problem with this kind of sheet and no AI solves it.

Ask for formulas, not a magic automation

With the invented copy in front of you, ask for exactly what you need: a column that works out the days waiting and a flag that appears by itself when they pass the limit you choose.

I have a spreadsheet in Excel / Google Sheets with a pharmacy’s
customer orders. Columns: A = order no., B = arrival date, C = product,
D = phone, E = status (waiting / notified / collected / returned),
F = date notified.

I want ONLY formulas, no macros or scripts:
1. Column G = days waiting since arrival, only if status is "waiting"
   or "notified"; blank otherwise.
2. Column H = "CALL" if status is "waiting" and it has been more than
   5 days; "RETURN" if status is "notified" and the date notified is
   more than 15 days ago; blank in any other case.
3. If the arrival date is missing or is not a date, G should show "CHECK DATE".
4. A way to sort the sheet so that "RETURN" rows come first, then "CALL".

Explain each formula in one line. Here are 10 INVENTED example rows:
paste the invented copy here

"Only formulas, no macros" is deliberate. A formula is visible, understandable and does nothing on its own; a macro or script can write, delete or send, and that is a different level of risk that this problem does not need.

Test it on the copy before touching the real sheet

Paste the formulas into the invented copy and check row by row that the result is what you expect. The typical failures are always in the same places: a date stored as text that gives an error instead of "CHECK DATE", a notified order that comes up as "CALL" because the formula only looked at the arrival date, or a day count that is out by one depending on whether today is counted.

When the copy behaves correctly with the five awkward cases, then you copy the formulas into the real sheet. Not the other way round: if something fails in the real sheet, you will not know whether it is the formula or your data.

And do an acid test: change an order’s status to "collected" by hand and check the flag disappears. A flag that does not go away once the problem is solved means that within two weeks nobody looks at the column.
The reminder to the customer is decided and made by a person

The sheet tells you who needs calling; it does not call. That is on purpose. Whether to phone, send a message or wait another day depends on things the sheet does not know: whether it is an urgent product, whether it is an older person living alone, whether they already came in and forgot.

Where AI does help is with the wording of the message, written once and used the same way every time. And the message does not say what the order is: "your blood pressure medicine has arrived" on a phone’s lock screen can be read by anyone standing nearby.
```
Write a 2-line message telling a customer that their order no.
number is ready at pharmacy name. Do not say what the product is
or what it is for. Include the opening hours: hours.
Friendly, brief tone.
```
And send it by message only to people who agreed to be contacted that way when they placed the order. The WhatsApp templates case explains how to collect that permission without any fuss.

A full example, and what is wrong with it

This is what a model proposed when it was given the real list of orders — with names, phone numbers and products — and asked to "automate the reminder for orders that are not collected". It is shortened and the details have been changed.

What it replies

"I suggest a script that runs every morning and does the following:

1. Reads the orders sheet and works out the days since the order date.
2. If more than 7 days have passed, automatically sends a WhatsApp: "Hi Maria, your thyroid medicine (levothyroxine 75) is now available at the pharmacy".
3. If more than 30 days have passed, deletes the row from the sheet to keep it tidy.

For example, for the orders you sent me, messages would be sent today to Maria G., Jose L. and Antonia R."

And now, what is wrong with that reply:

You have sent health data about identified people to an outside service. The real list, with names, phone numbers and products, is already in the chat. "Maria, levothyroxine" is an implied diagnosis with a name and a number. The failure is not in the reply: it is in the question, which is why step 2 exists.
The message says what the order is. "Your thyroid medicine (levothyroxine 75)" in a phone notification can be read by anyone nearby. Even if Maria had agreed to receive messages, she did not agree to have her treatment appear on her lock screen.
It counts from the wrong date. It works out the days "since the order date", not since the product arrived. An order that took five days to arrive would get the "you have not collected it" message two days after reaching the pharmacy. It is the date-column error from step 1.
It deletes data without anyone deciding. "Delete the row after 30 days" makes exactly the orders that need most attention disappear: the ones that should go back to the supplier, or belong to someone who could not come in. An automation that deletes on its own is the one that does most harm when it gets something wrong — and nobody finds out it did.

A technically simple proposal that fails on everything that matters: privacy, the content of the message, the reference date and automatic deletion. And the first of those failures had already happened before anyone read the reply.

With the invented copy and the prompt from step 3, the same model gave two formulas and a sort order, with no scripts, no sending and no deleting. The pharmacy still phones by hand; the difference is that it now knows who to phone, every morning, in ten seconds.

And if your pharmacy is not like that

If your pharmacy software already tracks orders. Check first whether it has a list of pending orders by age; many do and nobody uses it. If not, export that list to a sheet once a week and apply the same formulas. What you do not do is connect the pharmacy system to an AI service.

If what piles up is prescriptions waiting to be dispensed, not orders. It is the same pattern, but with even more care: it is prescription data, and there are validity periods that are not yours to set. The sheet can help you sort, but the deadlines come from the prescription rules, not from a formula an AI gave you.

If you want the reminder to go out automatically one day. That is possible, but it is a different project: explicit permission from each customer, a sending system that handles opt-outs and a message that says nothing about the product. And even then, with someone checking the list before it goes. The case on your first read-only agent is the sensible intermediate step.

When it does not work first time

The formula gives an error on some rows.

It is almost always dates stored as text. Select the date column and change the format to date; if they still fail, they were typed in different ways. Make them consistent by hand once and, from then on, have the column accept only dates.

It flags "CALL" on orders I have already notified.

The date-notified column is missing or is not being filled in. The formula cannot know you phoned if nobody wrote it down. Entering the date when you notify is the only new habit this sheet demands, and without it, it does not work.

The team does not fill in the dates.

Then the problem is not technical. Make the column compulsory in practice: an order with no arrival date shows up in red with "CHECK DATE" at the very top. Nobody wants to see their order in red every morning.

It suggests macros or scripts even though I ask for formulas only.

Say it again and do not use them. For this problem, formulas are enough and cannot do anything you do not see. When the sheet has been working for months and you want to take the next step, you do it knowing what you are doing, not because the chat offered.

Before you use what it gave you

Only an invented copy with the same columns has been in the chat; no real names, numbers or products.
Days are counted from when the product arrived at the pharmacy, not from the order date.
The sheet uses only formulas: it does not send, write or delete anything on its own.
The formulas were tested on the awkward cases before being moved to the real sheet.
The message to the customer does not say what the product is, and goes only to people who agreed to receive it.

The real list gets pasted "so it understands the problem". Writing a formula needs no real data at all. Anything pasted for convenience has already left.

It automates more than you ask. You ask for flagging and it offers sending and deleting. The more automatic action, the more harm when it goes wrong, and the harder it is to notice.

Messages that say too much. To make the reminder "useful", it names the product. On a phone notification, that is publishing health information.

Now, with your own. This week, add arrival-date and date-notified columns to your orders sheet or notebook, even if by hand. Once you have ten orders recorded that way, make the invented copy and run it through the prompt from step 3. In one afternoon you will have a sheet that tells you every morning, in its first row, which bag on the shelf needs someone.

← All practical cases Back to the school →

The orders nobody collects: a sheet that flags them by itself, without sending names anywhere

Step by step

Decide what information each order needs

Make an invented copy to work on with AI

Ask for formulas, not a magic automation

Test it on the copy before touching the real sheet

The reminder to the customer is decided and made by a person

A full example, and what is wrong with it

And if your pharmacy is not like that

When it does not work first time

Before you use what it gave you