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 ID | Customer | SKU | Line amount | Status |
|---|---|---|---|---|
| DD1001 | Emma Chen | SKU-A | 199 | Awaiting shipment |
| DD1001 | Emma Chen | SKU-A | 199 | Awaiting shipment |
| DD1001 | Emma Chen | SKU-A | 199 | Shipped |
| DD1001 | Emma Chen | SKU-B | 59 | Shipped |
| DD1002 | Noah Lee | SKU-C | 88 | Shipped |
| DD1002 | Noah Lee | SKU-C | 88 | Shipped |
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 compared | Rows kept | Output file |
|---|---|---|
| All five columns | 4 | orders-unique-rows.xlsx |
| Order ID | 2 | orders-unique-by-order-id.xlsx |
| Order ID and SKU | 3 | orders-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.