From a cluttered workbook to a 400-page reference
A Python workflow that cleaned a quotations collection spread across Excel worksheets, removed true duplicates while keeping every category a quote belonged to, and generated a formatted, navigable Word document.
A Python workflow that cleaned a quotations collection spread across Excel worksheets, removed true duplicates while keeping every category, and generated a formatted Word document.
E
“Nothing great was ever achieved without enthusiasm.”
Motivation · Character
F
“Well done is better than well said.”
Action · Character
“Lost time is never found again.”
Time
“Nothing great was ever achieved without enthusiasm.”
Motivation · Character
“Well done is better than well said.”
Action · Character
- Client
- Upwork client; identity private
- Built with
- Python, pandas and python-docx
- Timeline
- Three days, August 2025
- Delivered
- Cleaned workbook, 400+ page Word document, duplicates report and the code
An Upwork client; identity private. Built in three days in August 2025 with Python, pandas and python-docx.
The challenge
The client kept a large collection of quotations in an Excel workbook, one worksheet per category. Over time the same quotation had been entered in several places: sometimes because it really belonged to more than one category, sometimes as a plain copy, and sometimes with slightly different punctuation or author spelling.
They wanted two things: a clean version of the collection, and a reference document organized by author that they could actually read and navigate.
Quotations lived in one worksheet per category. Some were repeated because they belonged to several categories, some were plain copies, and some differed only by a comma or an author's spelling.
Categories
Worksheets were the categories
A quote's category was only recorded by which sheet it sat in. Merging copies risked losing that information.
Merging copies risked losing them.
Duplicates
Same, similar or different?
Some repeats were exact, some differed by a comma, and some looked alike but were genuinely different quotations.
Not every near-match is a copy.
Scale
Hundreds of pages to format
Organizing and styling a document this size by hand would mean days of repetitive editing, with plenty of room for inconsistency.
Days of repetitive editing by hand.
Before and after
Three workbook rows become one entry. An illustration using a public-domain quotation.
| Worksheet | Author | Quotation |
|---|---|---|
| Action | Benjamin Franklin | Well done is better than well said. |
| (same quote, second category): Character | Benjamin Franklin | Well done is better than well said. |
| (near-copy): Action | B. Franklin | Well done is better than well said |
“Well done is better than well said.”
Action · Character
Source workbook
- ActionBenjamin Franklin · Well done is better than well said.
- (same quote, second category): CharacterBenjamin Franklin · Well done is better than well said.
- (near-copy): ActionB. Franklin · Well done is better than well said
Yellow: one quote in two categories. Pink: a near-copy.
In the document
“Well done is better than well said.”
Action · Character
A quote and its categories are separate facts
The key modeling decision: treat "which quotation is this" and "which categories is it in" as two different pieces of information. That let the cleanup remove copies without losing any of the classification the client had built up.That decision let the cleanup remove copies without losing any classification.
Finding duplicates, and showing the work
Exact matching caught identical entries. Fuzzy matching caught near-copies that differed in punctuation, spacing or small wording changes, and gave each pair a similarity score.
Similar isn't always redundant. A near-match might be a typo, a shortened version, or a genuinely different quotation. So the scores went into a separate duplicates report the client could review, instead of the script silently deciding.
Exact matching caught identical entries; fuzzy matching scored near-copies. The scores went into a duplicates report the client could review, because similar isn't always redundant.
| Quotation A | Quotation B | Similarity |
|---|---|---|
| Well done is better than well said. | Well done is better than well said | 98 |
| Lost time is never found again. | Lost time is never found again! | 97 |
| Well begun is half done. | Well done is better than well said. | 61 |
- "…well said." vs "…well said"similarity 98
- "…found again." vs "…found again!"similarity 97
- "Well begun is half done." vs "Well done is…"similarity 61
Illustration with public-domain sayings. The last pair only looks alike.
Authors
One name per person
Variations like "B. Franklin" and "Benjamin Franklin" were normalized, so one author's quotations stayed together.
Missing values
No "nan" in the document
Empty spreadsheet cells can surface as technical placeholders. The generator made sure none appeared as an author name or anywhere a reader would see it.
One name per author
"B. Franklin" and "Benjamin Franklin" were normalized, and empty cells never appear as "nan".
A document built to be used
The Word document was generated entirely by code, so every one of its 400-plus pages follows the same structure and styling.
Grouped by author
Every quotation from the same person together, wherever it came from in the workbook.
Wherever the quote came from.
Sorted by last name
So it reads like a reference book, not the spreadsheet's row order.
Reads like a reference book.
Categories under each quote
Every theme a quote belongs to, shown once.
Every theme, shown once.
Real Word headings
Heading 1 and 2 styles, so Word's navigation pane works.
Word's navigation pane works.
A live table of contents
Built from those headings and set to update when the file opens.
Updates when the file opens.
Rebuildable any time
If the collection changes, the script produces a fresh, consistent document.
Amazing to work with, she went above and beyond. She far exceeded my expectations.
What was delivered
400+
pages of formatted, navigable Word reference
pages in Word
3 days
from the source workbook to delivery
to delivery
4
deliverables: cleaned workbook, Word document, duplicates report and the code
deliverables
A cleaned workbook, the Word document, a duplicates report and the code that produced them.
| Area | Before | After |
|---|---|---|
| Duplicates | Exact and near-copies scattered across sheets | Removed, with a scored report to review |
| Categories | Implied by which worksheet a quote sat in | Listed under each quote, all kept |
| Authors | Several spellings of the same name | One normalized name, sorted by last name |
| Reading it | Scrolling worksheets | A styled document with a table of contents |
This was data cleanup and document production, not a live system. Comparing and organizing the supplied quotations is not the same as verifying their wording or authorship, and the work made no claim to do so.
Data cleanup and document production, not a live system. Organizing quotations is not the same as verifying their wording or authorship.
The same pattern, other industries
Any organization with a spreadsheet that grew messy and needs a polished document from it.
Law firms
Clause and precedent librariesClause libraries
Standard clauses tracked in a spreadsheet, deduplicated and published as a navigable Word reference.
Accounting firmsAccounting
Engagement letter templatesEngagement letters
Service descriptions and terms kept in one sheet, assembled into consistent Word documents.
HR and operationsHR
Policy handbooks
Policies maintained as rows, published as a formatted handbook with a table of contents.
Nonprofits
Grant boilerplate
Reusable program descriptions by theme, without duplicate versions drifting apart.
Distributors
Product catalogs
A product spreadsheet with items in several categories, published as a printable catalog.
Training teamsTraining
Reference manuals and FAQsManuals and FAQs
Questions and answers collected over time, cleaned and organized into one manual.
Technical detail
- Python
- pandas
- python-docx
- Fuzzy matching
- Microsoft Excel
- Microsoft Word
Cleaning the workbook
pandas read every worksheet, recorded each row's sheet as its category, normalized author names and handled missing values. The cleaned collection was written back to Excel as its own deliverable.
Exact and fuzzy duplicate detection
Identical text was matched directly. Near-duplicates were compared with a similarity score, and every flagged pair went into a standalone report so the decisions could be checked.
Consolidating categories
When the same author and quotation appeared in several worksheets, the generator rendered it once and listed every sheet it came from as its categories.
Generating the Word document
python-docx built the document with Heading 1 and Heading 2 styles, inserted a Word table-of-contents field, and set the file to ask Word to update fields on opening so the contents reflect the generated pages.
Cleaning the workbook
Each sheet read as a category, authors normalized, missing values handled, cleaned collection saved back to Excel.
Duplicate detection
Exact matches plus scored near-matches, all listed in a standalone report.
Generating the document
python-docx with Heading 1 and 2 styles, a table-of-contents field, and fields set to update on opening.
Have a spreadsheet that should be a document?
Tell me what's in it and what the finished document needs to look like. We can discuss cleaning it up and generating it consistently.
Tell me what's in it and what the finished document needs to look like.
See other case studies