Library › Data AI › Office and admin

Flag repeat invoice numbers without flagging the first one

  • For Office and admin
  • You practice Setting limits and rules
  • Time about 5 minutes

About this exercise

You want column F of the Invoices sheet to say Duplicate on the second and later copies of an invoice number in column A, while the first copy stays blank so it still gets paid. Ask for one formula for F2 that works when filled down to F500, in one code block, then exactly 2 numbered lines labeled Flags: (in plain words) and Check: (what rows 2 and 5 will show). Under 45 words outside the code.

Common mistakes when you ask AI for this

  • Not saying the first copy stays blank. The AI counts the whole column, so every copy gets flagged and the vendor isn't paid. Try: Add: "Flag the second and later copies only; the first stays blank."
  • Leaving the output open. The AI returns TRUE/FALSE or a count, but AP looks for a blank. Try: Add: "Show Duplicate or a blank."
  • No check rows. Without known answers, you can't tell if it works. Try: Add: "Check: what rows 2 and 5 will show."
  • Getting a menu of options. The AI likes to offer two or three versions of the formula, so you're left to pick and test them yourself. Try: Add: "Give one formula only, in one code block, no alternatives."
  • Getting a tutorial. By default the AI explains every part of the formula at length. Try: Add: "Each line under 15 words. Nothing else."

What a strong prompt includes

  • Keeps the first copy blank
  • Says what to show
  • Mentions filling down
  • Asks for check rows
  • Asks for one formula

Practice this now ↑

Related exercises