Find Near-Duplicate Rows in Excel with Python and AI

You have a customer list with 40 rows and want to check it for duplicates. You start with the obvious tests. Every customer ID is unique. Every company name is unique. Every email address is unique. Every phone number is unique. There are no exact duplicate rows.

The file looks clean.

But ten customers appear twice.

The second version is just slightly different. One company has a different spelling, another uses an abbreviation, a phone number is formatted differently, or a contact name is shortened. A normal exact duplicate check cannot find these records because the values are not actually identical.

Those extra records affect the numbers too. In this demo file, the duplicated customers add 683,350 USD to the reported annual customer spend.

In this tutorial, we will find those near-duplicates with Python and RapidFuzz in MLJAR Studio. We will not ask AI to find and remove everything in one prompt. Instead, we will run the normal duplicate checks, find similar company names, inspect the matching records, test the similarity threshold, and only then decide which rows to keep.

That is especially important with fuzzy matching. A similarity score can tell us which records deserve attention, but it cannot decide by itself whether two companies are really the same customer.

You can open the full MLJAR Studio conversation to review every prompt, generated Python cell, and output used in this tutorial.

Why exact duplicate checks are not enough

A normal duplicate check asks a simple question: are these two values exactly the same?

For example:

Mueller Technik GmbH
Muller Technik GmbH
Mueller Technik GmbH
Muller Technik GmbH

To us, those names look like they probably refer to the same company. To Python, they are two different strings. The same thing happens with names such as:

GreenLeaf Foods Sp. z o.o.
Green Leaf Foods Sp z oo
GreenLeaf Foods Sp. z o.o.
Green Leaf Foods Sp z oo

or:

Northwind Analytics Limited
Northwind Analytics Ltd
Northwind Analytics Limited
Northwind Analytics Ltd

drop_duplicates() will not match these values, and that is correct: they are not exact duplicates. If your records really are identical, use the safer exact duplicate workflow first.

For this kind of data, we need to ask a different question: how similar are these two values?

Fuzzy matching gives us a similarity score rather than a simple yes or no. We can use that score to create a shortlist of records that may represent the same company, then inspect them before changing the data.

Get the data

Download the demo workbook from datasets-for-start and save it in:

~/Documents/
~/Documents/

The file is called companies-with-duplicates.xlsx. It contains 40 rows and ten columns. Ten customers were entered a second time with slightly different values. The changes are intentionally small because that is what makes near-duplicates difficult to spot in real customer lists.

Everything runs in the desktop app

MLJAR Studio is a desktop application built on JupyterLab. The workbook stays on your computer, and the generated Python reads it directly from your disk. The matching, comparisons, totals, and Excel export all run locally—there is no spreadsheet upload step.

The AI model is separate from the Python runtime. If you choose a cloud AI provider, Studio can send the prompt and relevant context such as column names, dataframe shape, and preview values to that provider so it can write useful code. For a customer file containing names, emails, addresses, and phone numbers, you should consider whether that context is appropriate to share.

For a fully private workflow, connect Studio to a local model through Ollama, vLLM, LM Studio, Jan, or llama.cpp. Then the prompt, data context, generated code, and results can all remain on your hardware. The local versus cloud LLM guide explains the deployment and privacy tradeoffs in more detail.

Open the Excel file

Start MLJAR Studio and choose Start a New AI Conversation.

Use Read a File and select companies-with-duplicates.xlsx.

Reading is not uploading. Studio opens the workbook from your local disk, and Python performs the analysis on your computer.

Studio generates Python to open the workbook:

import pandas as pd

file_path = "~/Documents/companies-with-duplicates.xlsx"
df = pd.read_excel(file_path)

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(
    f"Columns ({len(columns)}):",
    columns if len(columns) <= 100 else columns[:100] + ["..."]
)
df.head()
import pandas as pd

file_path = "~/Documents/companies-with-duplicates.xlsx"
df = pd.read_excel(file_path)

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(
    f"Columns ({len(columns)}):",
    columns if len(columns) <= 100 else columns[:100] + ["..."]
)
df.head()

The result is:

Shape: (40, 10)
Shape: (40, 10)

The file contains customer ID, company name, contact name, email, phone, address, city, country, annual spend, and signup date.

Before trying fuzzy matching, we should check whether ordinary duplicate detection already finds anything.

Start with the normal duplicate checks

Ask Studio:

how many exact duplicate rows are there, and are any Customer_ID,
Company_Name, Email or Phone values repeated
how many exact duplicate rows are there, and are any Customer_ID,
Company_Name, Email or Phone values repeated

Studio generates:

check_columns = ["Customer_ID", "Company_Name", "Email", "Phone"]

exact_duplicate_rows = int(df.duplicated().sum())
print(
    f"Exact duplicate rows (excluding the first occurrence): "
    f"{exact_duplicate_rows}"
)

for column in check_columns:
    counts = df[column].value_counts(dropna=False)
    print(column, "repeated values:", (counts > 1).sum())
check_columns = ["Customer_ID", "Company_Name", "Email", "Phone"]

exact_duplicate_rows = int(df.duplicated().sum())
print(
    f"Exact duplicate rows (excluding the first occurrence): "
    f"{exact_duplicate_rows}"
)

for column in check_columns:
    counts = df[column].value_counts(dropna=False)
    print(column, "repeated values:", (counts > 1).sum())

The result looks completely clean:

Exact duplicate rows: 0
Customer_ID   repeated values: 0
Company_Name  repeated values: 0
Email         repeated values: 0
Phone         repeated values: 0
Exact duplicate rows: 0
Customer_ID   repeated values: 0
Company_Name  repeated values: 0
Email         repeated values: 0
Phone         repeated values: 0

There are no exact duplicate rows, and none of the four fields contains an exact repeated value. This tells us that drop_duplicates() is not going to solve this problem.

It is also where it would be easy to stop. The file passed the usual checks, so we might assume there are no duplicates. Instead, we will now look for values that are similar rather than identical.

Find similar company names

Ask:

install rapidfuzz and find pairs of company names that are similar but not identical
install rapidfuzz and find pairs of company names that are similar but not identical

RapidFuzz is a fast, MIT-licensed string-matching library for Python and C++. It provides several similarity metrics for comparing text that is close but not identical.

This tutorial uses token_sort_ratio(). It splits each value into tokens, sorts those tokens, and returns a similarity score from 0 to 100. A higher score means the two strings are more similar. This makes the comparison less sensitive to word order, but the score is still only evidence for review—it is not a duplicate decision.

Studio installs RapidFuzz into the notebook environment with %pip, then uses it to compare the company names. Once the package is installed in that environment, the same import and matching code can be rerun from the saved notebook without asking AI to generate it again.

The generated code is:

%pip install -q rapidfuzz

from itertools import combinations
from rapidfuzz.fuzz import token_sort_ratio

company_names = (
    df["Company_Name"]
    .dropna()
    .astype(str)
    .drop_duplicates()
    .tolist()
)

similar_pairs = [
    {
        "Company_Name_1": name_1,
        "Company_Name_2": name_2,
        "Similarity_Score": round(
            token_sort_ratio(name_1, name_2), 1
        ),
    }
    for name_1, name_2 in combinations(company_names, 2)
    if name_1 != name_2
    and token_sort_ratio(name_1, name_2) >= 70
]

similar_company_pairs = (
    pd.DataFrame(similar_pairs)
    .sort_values("Similarity_Score", ascending=False)
    .reset_index(drop=True)
)

similar_company_pairs
%pip install -q rapidfuzz

from itertools import combinations
from rapidfuzz.fuzz import token_sort_ratio

company_names = (
    df["Company_Name"]
    .dropna()
    .astype(str)
    .drop_duplicates()
    .tolist()
)

similar_pairs = [
    {
        "Company_Name_1": name_1,
        "Company_Name_2": name_2,
        "Similarity_Score": round(
            token_sort_ratio(name_1, name_2), 1
        ),
    }
    for name_1, name_2 in combinations(company_names, 2)
    if name_1 != name_2
    and token_sort_ratio(name_1, name_2) >= 70
]

similar_company_pairs = (
    pd.DataFrame(similar_pairs)
    .sort_values("Similarity_Score", ascending=False)
    .reset_index(drop=True)
)

similar_company_pairs

This finds ten pairs:

Some differences are tiny. One value has an extra space, another uses punctuation, one uses Ltd instead of Limited, and another has a typo. None of those differences is unusual when information has been entered manually on different days.

The code uses combinations() to compare every company name with every other company name. With 40 names, that means 780 comparisons. We keep only pairs with a similarity score of at least 70.

The important word is pairs. We have not removed anything. We have created a list of records worth checking.

Check the other columns before deciding

A similar company name is evidence, but it is not proof. Two different companies can have similar names, so we should inspect the other information before merging anything.

Ask:

show the matches with Contact_Name, Email, Phone, City,
Annual_Spend_USD and Signup_Date for both rows side by side
show the matches with Contact_Name, Email, Phone, City,
Annual_Spend_USD and Signup_Date for both rows side by side

Consider the ACME pair:

The company names are very similar. The contact is Robert Clark in one row and Rob Clark in the other. Both records are in Chicago, the phone numbers are close, and the signup dates are one day apart. Taken together, the fields strongly suggest that this is one customer entered twice.

The other matched pairs show the same kind of pattern.

This is the most important part of the workflow. RapidFuzz finds candidates; the other columns and a person who understands the data determine whether those candidates represent the same customer. A score is useful for deciding what to inspect, not for replacing the business decision.

Try a different similarity threshold

We used a threshold of 70. That number controls how similar two names must be before they appear in the results.

Ask:

how many pairs would I find with a threshold of 75 instead of 70
how many pairs would I find with a threshold of 75 instead of 70

The result drops from ten pairs to seven. Three pairs disappear:

Nova Retail Group / NOVA Retail Grp.              72.7
Blue Peak Consulting / BluePeak Consulting GmbH  72.7
Horizon Bio Labs / Horizon Biolabs                71.0
Nova Retail Group / NOVA Retail Grp.              72.7
Blue Peak Consulting / BluePeak Consulting GmbH  72.7
Horizon Bio Labs / Horizon Biolabs                71.0

In this demo dataset, all three are real near-duplicates. Raising the threshold from 70 to 75 would therefore miss three of the ten duplicate pairs.

A lower threshold creates the opposite problem. As matching becomes more permissive, genuinely different companies can enter the candidate list.

There is no universal threshold that works for every dataset. Names, addresses, products, and customer records behave differently. Working interactively lets us change the threshold, inspect the matches, and decide whether it is too strict or too loose for this data.

See how duplicate customers affect the numbers

Now that we have reviewed the ten pairs, let us measure their effect on annual spend.

Ask:

what is the total Annual_Spend_USD now, and what would it be if each
pair counted once keeping the row with the earlier Signup_Date
what is the total Annual_Spend_USD now, and what would it be if each
pair counted once keeping the row with the earlier Signup_Date

Studio reports:

Current total Annual_Spend_USD:               2,225,500.00 USD
Total after counting each matched pair once:  1,542,150.00 USD
Current total Annual_Spend_USD:               2,225,500.00 USD
Total after counting each matched pair once:  1,542,150.00 USD

The difference is 683,350.00 USD.

Nothing in the original spreadsheet looked broken. All 40 rows were valid rows. The problem was that ten customers were represented twice.

Notice that we specified a rule: keep the row with the earlier Signup_Date. That rule matters because the paired rows are not identical. If we keep a different row, the final total can change.

When deduplicating near-matches, “which record should survive?” is a business rule. State it explicitly rather than leaving a tool to guess.

Merge the pairs and save the result

Once we are comfortable with the matches and the selection rule, we can create the cleaned dataframe.

Ask:

merge each pair into one row, keeping the row with the earlier Signup_Date,
then save as companies-deduplicated.xlsx
merge each pair into one row, keeping the row with the earlier Signup_Date,
then save as companies-deduplicated.xlsx

The generated code finishes with:

deduplicated_df = (
    df.loc[~df["Company_Name"].isin(remove_names)]
    .sort_values("Signup_Date")
    .reset_index(drop=True)
)

output_path = "companies-deduplicated.xlsx"
deduplicated_df.to_excel(output_path, index=False)

print(f"Saved {len(deduplicated_df)} rows to: {output_path}")
print(
    f"Annual_Spend_USD total: "
    f"{deduplicated_df['Annual_Spend_USD'].sum():,.2f} USD"
)
deduplicated_df = (
    df.loc[~df["Company_Name"].isin(remove_names)]
    .sort_values("Signup_Date")
    .reset_index(drop=True)
)

output_path = "companies-deduplicated.xlsx"
deduplicated_df.to_excel(output_path, index=False)

print(f"Saved {len(deduplicated_df)} rows to: {output_path}")
print(
    f"Annual_Spend_USD total: "
    f"{deduplicated_df['Annual_Spend_USD'].sum():,.2f} USD"
)

The output confirms:

Saved 30 rows to: companies-deduplicated.xlsx
Annual_Spend_USD total: 1,542,150.00 USD
Saved 30 rows to: companies-deduplicated.xlsx
Annual_Spend_USD total: 1,542,150.00 USD

We started with 40 rows. Ten customers appeared twice, so after keeping one row from each pair we have 30 customer records. The total matches the value calculated before changing the data, confirming that the final operation followed the reviewed rule.

In this demo file, keeping the earlier record also keeps the more complete contact information. That pattern is specific to this dataset. With your own data, you may prefer the latest record, the record with the most complete fields, or a merged record that combines information from both rows.

The output filename does not include a directory, so the file is saved relative to the notebook's working directory. If you want it in ~/Documents/, include that location in the prompt. The original workbook remains unchanged.

Fuzzy matching needs human review

This example is deliberately small, which makes the matching easy to inspect. Real customer lists are usually more complicated.

A high similarity score can still be a false positive. A parent company and a subsidiary may have nearly identical names. Two branches of the same chain may look similar but need to remain separate records. A company name shared by businesses in different cities may also produce a high score.

That is why the company name should not be the only signal. Phone numbers, email domains, cities, addresses, and signup dates can provide additional evidence. A similar name plus the same normalized phone number is much stronger evidence than a similar name by itself.

The way records should be merged also depends on the data. In this tutorial, keeping one row is enough. Elsewhere, one copy may contain an email address while another contains a phone number. The best result may be a new record that combines useful fields from both.

For larger datasets, consider performance too. Comparing 40 names requires 780 comparisons. Comparing every pair among 10,000 records requires roughly 50 million. At that size, narrow the candidates first—for example, compare companies only within the same country or city—or use more efficient RapidFuzz functions.

The workflow remains the same: find candidates, inspect them, decide the rule, and only then change the data.

Reuse the conversation as Python code

The conversation does not disappear when the analysis is finished. MLJAR Studio stores the prompts as markdown cells, the generated Python as code cells, and the outputs together in a normal Jupyter notebook on your computer.

You can switch to the notebook view, inspect or edit every generated cell, and rerun the workflow without asking the AI to recreate it. The conversation has effectively become reusable Python code.

That is particularly useful for fuzzy matching because thresholds and matching rules are decisions you may revisit. You might later change 70 to 68, normalize phone numbers as another signal, or add blocking by country. Change the relevant cell and rerun the later steps.

When the next customer export arrives, update the input path and run the notebook again. The exact checks, fuzzy matching, review tables, total comparison, and export remain documented in one repeatable desktop workflow.

This is why we did not use a single prompt such as find all duplicates and remove them. That would hide the important decisions. With near-duplicates, we want to see why records were matched and decide for ourselves whether they should become one.

Privacy recap

• The desktop app runs Python locally. Reading the workbook, comparing names, calculating totals, and saving the result happen on your computer.
• A cloud AI model receives context. When a cloud provider writes the code, relevant prompts, schema information, and preview values may be sent to that provider; the workbook itself is not uploaded merely because Python reads it.
• A local LLM keeps the AI step private. With Ollama, vLLM, LM Studio, Jan, or llama.cpp, the prompts and data context can stay on your own hardware too.

Choose the model setup that matches the sensitivity of the customer data you are reviewing.

What we did

We started with a customer workbook containing 40 rows. Exact duplicate checks found nothing, and customer IDs, company names, emails, and phone numbers were all unique.

Then we changed the question from “are these values identical?” to “how similar are these company names?”

RapidFuzz with a threshold of 70 produced ten candidate pairs. We inspected other customer fields before deciding that the pairs represented the same companies, then tested a stricter threshold and saw that it would miss three real matches.

Finally, we calculated the effect on annual customer spend, chose an explicit rule for which record to keep, and saved a cleaned workbook containing 30 customers instead of 40.

The key part is not the similarity score itself. Fuzzy matching helps you find records worth looking at. The final decision still comes from understanding what the rows mean.

Next steps

For a concise comparison of both approaches, see Find Duplicate Rows in Excel.

If your file contains exact repeated rows, start with Remove duplicate rows from Excel before using fuzzy matching.

For a broader workflow, follow Clean messy Excel data to handle duplicates, missing values, mixed dates, and inconsistent country names together.

Standardize text categories in Excel shows another case where values look different to a computer but mean the same thing to a person.

If colleagues need to run a reviewed spreadsheet workflow without editing notebook code, explore Python internal tools.

Browse the spreadsheet workflow hub for more ways to inspect, transform, automate, and share Excel data.