Portfolio
  • Data cleanup
  • Document generation
  • Python

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.

Illustration of the output format, with public-domain sample quotations.Illustration of the output, with public-domain quotations.
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.

Source workbook
WorksheetAuthorQuotation
ActionBenjamin FranklinWell done is better than well said.
(same quote, second category): CharacterBenjamin FranklinWell done is better than well said.
(near-copy): ActionB. FranklinWell done is better than well said
Yellow: same quote in two categories. Pink: a near-copy with a different author spelling and no period.

Franklin, Benjamin

“Well done is better than well said.”

Action · Character

One entry, filed under the author's last name, keeping both categories.

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

Franklin, Benjamin

“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.

Duplicates report (illustration)
Quotation AQuotation BSimilarity
Well done is better than well said.Well done is better than well said98
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
Public-domain examples. High scores are likely copies; the low score is two different sayings that only look alike.
  • "…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.

  1. Grouped by author

    Every quotation from the same person together, wherever it came from in the workbook.

    Wherever the quote came from.

  2. Sorted by last name

    So it reads like a reference book, not the spreadsheet's row order.

    Reads like a reference book.

  3. Categories under each quote

    Every theme a quote belongs to, shown once.

    Every theme, shown once.

  4. Real Word headings

    Heading 1 and 2 styles, so Word's navigation pane works.

    Word's navigation pane works.

  5. A live table of contents

    Built from those headings and set to update when the file opens.

    Updates when the file opens.

  6. 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.

★★★★★Client, verified Upwork review

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.

What was delivered: before and after
AreaBeforeAfter
DuplicatesExact and near-copies scattered across sheetsRemoved, with a scored report to review
CategoriesImplied by which worksheet a quote sat inListed under each quote, all kept
AuthorsSeveral spellings of the same nameOne normalized name, sorted by last name
Reading itScrolling worksheetsA 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