Find duplicate Excel rows and separate them for review

01Define what counts as a duplicate

In this example, rows are duplicates only when all four values match: order_id, customer, item, and quantity. Matching order numbers alone do not make rows duplicates. Spaces and letter case in strings are not changed automatically.

Before deleting anything, collect every row with matching contents in the Duplicates sheet, including the first occurrence. Rows that appear only once go into the Unique sheet so you can review all 8 input rows without omissions.

02Inspect the input file structure

The sheet in orders.xlsx must be named Orders, and its first row must have the column names and order shown below. Adding columns also causes a header error. Rather than replacing the example with a real work file straight away, create a practice file by copying just the four required columns.

Completely empty rows are skipped. The script stops if only some values are missing or a row contains a formula. Order numbers, customer names, and items must be non-empty strings. Numeric order numbers are not converted automatically; prepare them as text in the input file.

03Prepare the files and run the example

1. Extract the ZIP and check that the input file listed in README.txt is in the same folder as example.py. Do not run the example from inside the ZIP archive.
2. Run the commands below one line at a time in the folder containing example.py. On Windows, if python is unavailable but py is available, replace python in each command with py -3.12.
3. After the completion message appears, open the new outputs folder. If outputs already exists, first rename it to keep the previous results, then run the example again.

python --version
python -m pip install -r requirements.txt
python example.py

Installation requires an internet connection. BASE in the code sets the input and output paths relative to example.py, not the current terminal folder.

04Full code and processing order

The code opens the original with load_workbook in read-only mode and uses Counter to count how often each row occurs. It tracks the original Excel row numbers with enumerate and start=2. Results are saved to a new Workbook, so the original is not saved again.

"""Find repeated business rows. Keep the source workbook unchanged."""
from collections import Counter
from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill

BASE = Path(__file__).resolve().parent
HEADERS = ("order_id", "customer", "item", "quantity")


def main():
    destination = BASE / "outputs"
    if destination.exists():
        raise ValueError("outputs already exists; rename it before running again.")
    source = load_workbook(BASE / "orders.xlsx", read_only=True, data_only=False)
    try:
        if "Orders" not in source.sheetnames:
            raise ValueError("Orders sheet is missing.")
        worksheet = source["Orders"]
        rows = list(worksheet.iter_rows(values_only=True))
        if not rows or tuple(rows[0]) != HEADERS:
            raise ValueError(f"Expected headers: {HEADERS}")
        records = []
        for row_number, row in enumerate(rows[1:], start=2):
            if all(value is None for value in row):
                continue
            if any(value is None for value in row):
                raise ValueError(f"Row {row_number}: missing value.")
            if any(isinstance(value, str) and value.startswith("=") for value in row):
                raise ValueError(f"Row {row_number}: formulas are not supported.")
            if not all(isinstance(value, str) and value.strip() for value in row[:3]):
                raise ValueError(f"Row {row_number}: first three fields must be text.")
            if type(row[3]) is not int or row[3] <= 0:
                raise ValueError(f"Row {row_number}: quantity must be a positive integer.")
            records.append((row_number, tuple(row)))
        if not records:
            raise ValueError("No data rows were found.")
    finally:
        source.close()

    counts = Counter(row for _, row in records)
    repeated = [(number, row) for number, row in records if counts[row] > 1]
    unique = [(number, row) for number, row in records if counts[row] == 1]
    book = Workbook()
    book.remove(book.active)
    for name, selected in (("Duplicates", repeated), ("Unique", unique)):
        sheet = book.create_sheet(name)
        sheet.append(("source_row", *HEADERS, "occurrences"))
        for number, row in selected:
            sheet.append((number, *row, counts[row]))
        sheet.freeze_panes = "A2"
        sheet.auto_filter.ref = sheet.dimensions
        sheet.sheet_view.showGridLines = False
        for cell in sheet[1]:
            cell.font = Font(name="Arial", bold=True, color="FFFFFF")
            cell.fill = PatternFill("solid", fgColor="243B53")
        for column, width in zip("ABCDEF", (14, 16, 18, 20, 14, 16)):
            sheet.column_dimensions[column].width = width
    destination.mkdir(exist_ok=False)
    book.save(destination / "duplicate_review.xlsx")
    book.close()
    groups = sum(count > 1 for count in counts.values())
    print(f"Input rows: {len(records)}; duplicate groups: {groups}")
    print(f"Duplicate rows: {len(repeated)}; unique rows: {len(unique)}")
    print("Created outputs/duplicate_review.xlsx (source unchanged).")


if __name__ == "__main__":
    try:
        main()
    except (OSError, ValueError) as error:
        raise SystemExit(f"Stopped: {error}") from error

05Results created by the script

The script creates two sheets, Duplicates and Unique, in outputs/duplicate_review.xlsx. source_row is the Excel row number in the original, and occurrences is the number of times the same contents appear across the full input.

The 4 rows in Duplicates do not mean that 4 rows should be removed. If you keep one row from each group, there are 2 extra entries. Decide which to keep only after checking the originals.

06Check the results yourself

1. In Duplicates, check that source_row 2 and 5 both have O-1001 and occurrences of 2.
2. Check that source_row 3 and 8 both have O-1002.
3. Add the 4 Duplicates rows and 4 Unique rows, and check that they match the 8 input rows.
4. Reopen orders.xlsx and check that all 8 data rows remain unchanged.

07Common errors and how to fix them

08What this example does not cover

• It does not copy formatting, charts, macros, connections, or hidden-row states. The result contains only the values needed for review and basic formatting.
• It does not trim spaces, standardize letter case, or detect similar strings. If your definition of a duplicate changes, design separate comparison criteria.
• It collects the entire file in memory, so it does not guarantee performance on large files. Encrypted or damaged files are not supported.
• Editing values in the result file does not automatically recalculate the original or the results. To get new results, edit a copy of the original and run the script again.

Execution and verification record

Windows 11 (10.0.26200), CPython 3.12.14 (64-bit). openpyxl 3.1.5, Matplotlib 3.10.8. The libraries used by each example are pinned in requirements.txt.

• Verified row counts, original row numbers, and occurrence counts for valid data
• Confirmed the original file's SHA-256 hash was unchanged and overwriting was rejected on a repeated run
• Checked errors for mismatched headers, missing values, formulas, and invalid quantities

Site-wide writing and verification principles

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Official openpyxl tutorial — Reading and saving workbooks ↗ (New tab)
• Official Python 3.12 documentation — pathlib: file paths and new folders ↗ (New tab)