Skip to main content

Remove Duplicate Rows in Excel with Java (Compare All or Selected Columns)

When you combine order exports in Excel, some rows are exact copies. Others describe the same order line at different points in time. A line marked “Awaiting shipment” in the morning export might be “Shipped” in the afternoon. Two rows can also share an order ID because they list different products.

Which rows count as duplicates depends on what you want the output to represent. This tutorial shows how to remove duplicate rows in Excel with Java by comparing the entire row, a single column, or a combination of columns. Each example starts with the same order data, so you can see how the choice affects the result.


Set Up the Maven Project

Add the following repository and dependency to your project's pom.xml.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>16.10.1</version>
    </dependency>
</dependencies>

Prepare the Sample Workbook

Enter the data below in cells A1:E7 on the first worksheet of order-details.xlsx. Keep the sample table on its own worksheet because the examples delete worksheet rows. Save the workbook in the program's working directory.

Order IDCustomerSKULine amountStatus
DD1001Emma ChenSKU-A199Awaiting shipment
DD1001Emma ChenSKU-A199Awaiting shipment
DD1001Emma ChenSKU-A199Shipped
DD1001Emma ChenSKU-B59Shipped
DD1002Noah LeeSKU-C88Shipped
DD1002Noah LeeSKU-C88Shipped

Each example reads the original workbook and saves the result to a separate file.

Remove Exact Duplicate Rows

When the same data was imported twice, call sheet.removeDuplicates() to compare every column in the worksheet's used range. In this sample, two rows count as duplicates only if all five values in columns A through E match.

The no-argument call includes the header in the comparison. If a later row repeats the header's values, that row is removed as a duplicate. To skip the header, use the range overload starting at row 2.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class RemoveDuplicateRows {

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        try {
            workbook.loadFromFile("order-details.xlsx");
            Worksheet sheet = workbook.getWorksheets().get(0);

            // Compare every column in the worksheet's used range.
            sheet.removeDuplicates();

            workbook.saveToFile("orders-unique-rows.xlsx", ExcelVersion.Version2016);
        } finally {
            workbook.dispose();
        }
    }
}

The two identical “Awaiting shipment” rows for DD1001 collapse to one. The “Shipped” row stays because its status differs.

Remove Duplicates by One Column

If you need a list with one row per order, compare just the order IDs in column A. When several rows share an ID, the first one stays.

The first four arguments to removeDuplicates() define the range from A1 through column E on the last row. The true flag tells the method to treat the first row as a header. The last argument, new int[]{0}, selects the first column in the range. The array uses zero-based offsets relative to the range, so 0 points to column A here.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class RemoveDuplicatesByOrderId {

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        try {
            workbook.loadFromFile("order-details.xlsx");
            Worksheet sheet = workbook.getWorksheets().get(0);

            int lastRow = sheet.getLastRow();
            if (lastRow > 1) {
                // Skip the header and compare order IDs in column A.
                sheet.removeDuplicates(1, 1, lastRow, 5, true, new int[]{0});
            }

            workbook.saveToFile("orders-unique-by-order-id.xlsx", ExcelVersion.Version2016);
        } finally {
            workbook.dispose();
        }
    }
}

For DD1001, the output keeps the first SKU-A row and drops SKU-B. The amount shown is the amount from the retained line item.

Remove Duplicates by Multiple Columns

If each SKU should appear once per order, compare the order ID and SKU together. A row counts as a duplicate only when both values match a row that came before it.

Use the same overload with new int[]{0, 2} to compare the first and third columns of the range, A and C.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class RemoveDuplicatesByOrderAndSku {

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        try {
            workbook.loadFromFile("order-details.xlsx");
            Worksheet sheet = workbook.getWorksheets().get(0);

            int lastRow = sheet.getLastRow();
            if (lastRow > 1) {
                // Skip the header and compare order IDs in A and SKUs in C.
                sheet.removeDuplicates(1, 1, lastRow, 5, true, new int[]{0, 2});
            }

            workbook.saveToFile("orders-unique-by-order-id-and-sku.xlsx", ExcelVersion.Version2016);
        } finally {
            workbook.dispose();
        }
    }
}

Order DD1001 now keeps one row for SKU-A and one for SKU-B. If your data allows the same SKU on multiple legitimate lines within one order, use the order ID and line number as the key.

Compare the Results

The input has six data rows. The counts below exclude the header.

Columns comparedRows keptOutput file
All five columns4orders-unique-rows.xlsx
Order ID2orders-unique-by-order-id.xlsx
Order ID and SKU3orders-unique-by-order-id-and-sku.xlsx

Common Questions

How do I keep the most recent record?

The examples keep the first row for each set of matching values. If you have a timestamp column, sort from newest to oldest before running the single-column or multiple-column example. That puts the latest record first in each group.

Why did two IDs that look the same both stay in the worksheet?

Check for leading or trailing spaces. For example, "DD1001" and "DD1001 " differ by a trailing space and are treated as separate values. Trim the columns you're comparing before running the code if those spaces came from the export.

Comments

Popular posts from this blog

3 Ways to Generate Word Documents from Templates in Java

A template is a document with pre-applied formatting like styles, tabs, line spacing and so on. You can quickly generate a batch of documents with the same structure based on the template. In this article, I am going to show you the different ways to generate Word documents from templates programmatically in Java using Free Spire.Doc for Java library. Prerequisite First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file. <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>...

Insert and Extract OLE objects in Word in Java

You can use OLE (Object Linking and Embedding) to include content from other programs, such as another Word document, an Excel or PowerPoint document to an existing Word document. This article demonstrates how to insert and extract embedded OLE objects in a Word document in Java by using Free Spire.Doc for Java API.   Add dependencies First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use maven, you need to add the following code to your project’s pom.xml file.     <repositories>               <repository>                   <id>com.e-iceblue</id>                   <name>e-iceblue</name>    ...

Simple Java Code to Convert Excel to PDF in Java

This article demonstrates a simple solution to convert an Excel file to PDF in Java by using free Excel API – Free Spire.XLS for Java . The following examples illustrate two possibilities to convert Excel to PDF:      Convert the whole Excel file to PDF     Convert a particular Excel Worksheet to PDF Before start with coding, you need to Download Free Spire.XLS for Java package , unzip it and import Spire.Xls.jar file from the lib folder in your project as a denpendency. 1. Convert the whole Excel file to PDF Spire.XLS for Java provides saveToFile method in Workbook class that enables us to easily save a whole Excel file to PDF. import com.spire.xls.FileFormat; import com.spire.xls.Workbook; public class ExcelToPDF {     public static void main(String[] args){         //Create a Workbook         Workbook workbook = new Workbook();   ...