Prompt library Civil servants, Intermediate, Across government, Data extraction
Clean up a messy list or spreadsheet extract
Standardise names, dates and spellings in a list, with likely duplicates and missing values flagged and no rows deleted.
Last reviewed: . Reviewer: Muhammad Asadullah.
Before you paste anything: replace names, CNIC numbers and other personal details with placeholders and never use classified material. Use only AI tools your department allows.
When to use it
You have a list from several sources, such as a beneficiary list, an attendance sheet or a stock register. The names are spelt differently, dates are in mixed formats and some entries appear twice. You want a clean version you can check, without losing anything.
The prompt
Clean the list below. It is an extract from [what the list is, for example an attendance sheet].
Columns: [list the column names]
Data:
[Paste the rows. Remove or replace CNIC numbers, phone numbers and other personal details you do not need]
Do the following:
1. Standardise spellings of names, places and offices. Use the most common spelling in the list
2. Put all dates in the format [DD/MM/YYYY]
3. Add a column called "Notes" and use it to mark: likely duplicate (say which row it matches), MISSING (say which field) or [CHECK] (say what looks wrong)
4. Return the full table with the same number of rows as the original, in the same order
5. After the table, list every change you made, row by row
Never delete, merge or reorder rows. Never fill in a missing value by guessing. If you are not sure two spellings are the same person or place, mark [CHECK] instead of changing it.How to fill it in
- Remove personal data you do not need for the task before pasting. Replace names with codes if the cleaning does not depend on them.
- Give the date format your office uses.
- For a long list, work in batches of about 100 rows. Long tables are more likely to have rows dropped.
Before you use the result
- Count the rows. The cleaned table must have the same number as the original.
- Decide on each likely duplicate yourself. The AI only flags them.
- Spot-check several changed rows against the original source, not only against the extract.
Related guide: How to extract data from documents.