How to Remove Duplicates from Excel XLSX Using C# .NET and VB.NET

Removing duplicate data from Microsoft Excel files is one of the most underrated features! When an Excel file contains large amounts of data, there is a chance there are duplicate records which can be misleading when analyzing the data. Excel’s Remove Duplicate Rows feature allows users to pick which columns should be checked and remove duplicate data in the associated row.

In this blog, we are going to demonstrate how to remove duplicate rows in an Excel file programmatically in C# and VB.NET, how to invoke an Excel-like dialog during a .NET apps runtime to allow users to remove duplicate data in a familiar manner on the client side, and lastly, how developers can use that same Excel-like dialog within a code-free designer.

We will use Spread.NET, the world’s #1 selling .NET spreadsheet component, to accomplish this. Spread.NET delivers an Excel-like spreadsheet experience, with zero dependencies on Excel, to create enterprise spreadsheets, grids, dashboards, and forms with its comprehensive API.

Ready to Test it Out? Download Spread.NET Today!

What we will cover:

1. Remove Duplicate Rows in an Excel XLSX File Programmatically in C# and VB.NET
2. Invoke an Excel-like Dialog During a .NET Apps Runtime
3. Remove Duplicates with a Code-Free Designer

Remove Duplicate Rows in an Excel XLSX File Programmatically in C# and VB.NET

Spread.NET for WinForms allows users to permanently delete duplicate data from a selected range programmatically using the RemoveDuplicates method from the IRange interface and specifying the target range. In the example below, we will remove duplicate data programmatically from a list of contacts within an Excel .XLSX file.

C#:

int[] columns = { 1, 2 };
fpSpread1.AsWorkbook().ActiveSheet.Range("A3:G28").RemoveDuplicates(columns, YesNoGuess.No);

VB.NET:

Dim columns As Integer() = {1, 2}
fpSpread1.AsWorkbook().ActiveSheet.Range("A3:G28").RemoveDuplicates(columns, YesNoGuess.No)

Results from the above code:

Spread.NET users can also invoke an Excel-like UNIQUE formula function to return a list of all the unique values in a cell range; this option does not delete the duplicates. An end-user can apply this formula or programmatically by setting the formula to a cell. You can learn how to use Formulas in Cells with Spread.NET here in our documentation.

C#:

IWorkbook workbook = fpSpread1.AsWorkbook();
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;
fpSpread1.Sheets[1].Cells[2, 4].Formula = "UNIQUE(A3:B28)";

VB.NET:

Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All
fpSpread1.Sheets(1).Cells(2, 4).Formula = "UNIQUE(A3:B28)"

Invoke an Excel-like Dialog During a .NET Apps Runtime

New with the v16 release, Spread.NET now offers a powerful built-in Excel-like Remove Duplicates dialog. This runtime dialog can be invoked programmatically, allowing the user to remove duplicate values from a range of values with an interactive UI during a .NET app’s runtime.

C#:

activeSheet.Cells["A1:E7"].Select();
FarPoint.Win.Spread.Dialogs.BuiltInDialogs.RemoveDuplicates(fpSpread1).ShowDialog(fpSpread1);

VB.NET:

activeSheet.Cells("A1:E7").Select()
FarPoint.Win.Spread.Dialogs.BuiltInDialogs.RemoveDuplicates(fpSpread1).ShowDialog(fpSpread1)

Remove Duplicates with a Code-Free Designer

Spread.NET offers a Designer that allows developers to visually design a Spread component using a graphical Excel-like user interface. It also supports being deployed during runtime to allow users to modify spreadsheets in their application. The Spread Designer has many advanced features with a familiar Excel-like look and feel. This Designer saves developers time by removing the need to programmatically create and/or modify the Spread component.

Spread Designer provides a Remove Duplicates ribbon button under the Data Tools group of the Data tab.

Download a trial of Spread.NET to check out the Spread Designer!

In the below GIF, the Spread Designer is being accessed through Visual Studio 2022 during design time in a WinForms .NET 7 app. Notice that the changes applied in the Spread Designer using the Remove Duplicates dialog are observed during the app's runtime.

GrapeCity Spreadsheet Components

This article does not even scratch the surface of the full capabilities of the GrapeCity Spread.NET spreadsheet component. Download a free 30-day trial, review the documentation to see the many available features, and download the demo explorer to see the features in action and interact with the sample code.

Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program. To learn more about Spread.NET and the new features added with the v16 release, check out our release blog.

Ready to Test it Out? Download Spread.NET Today!