Remove Duplicates from Range

In Spread, the duplicate data can be highlighted using conditional formatting. However, if there is a large amount of data, removing duplicate data is always preferred to ease data analysis.

With Spread for WinForms, you can permanently delete the duplicate data from the selected range using the "Remove Duplicates" option. When you remove the duplicate data or values from the selected range, the only effect is on the values in the cell range. Other values outside the range do not change or move. When the duplicate data is removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Using Code

You can set the RemoveDuplicates method from IRange interface to remove the duplicate data from the range of cells by specifying the target range.

The following image shows the data before and after it has been modified using code:

C#

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

VB

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

Using Runtime UI

You can enable the built-in remove duplicates dialog box using the RemoveDuplicates method of the BuiltinDialogs class at run-time.

The following code example shows how to use the runtime dialog box in a Spread worksheet:

C#

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

VB

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

Using Designer

Spread for WinForms designer provides the "Remove Duplicates" ribbon button under the "Data" > "Data Tools" tab group.

The following GIF illustrates the removal of duplicates data from the selected range of data.

You can also choose to ignore the first row by selecting the "My Data has Headers" checkbox option.

Feature Interaction with Different Types of Values

1. With Formulas

   
   

   The formula in the cell after the duplicate cell is copied over. The following GIF illustrates the use of "Remove Duplicates" in the range of cells where a formula is applied for the Fibonacci series.

   
   

   As observed, when the "Remove Duplicates" feature is implemented, it removed 'B2' and copies 'B3' to 'B2' and each next cell moves forward by one, changing "B3" to "=B1+B2".

   
   

   
   

2. With Formatting

   If the value of a cell is the same as that of another cell in the select range, formatting and style are not considered as different cells, and they are removed along with the value.

   As observed, the cells have different formatting even though the value of the cells is 1,2, and 3. After implementing Remove Duplicates, all the same value cells are removed.

   
   

   
   

3. With Automatic Expansion Selection

   While using remove duplicates, if you select one or more columns of the table and click on remove duplicates, then the selection is expanded to select the whole table.

   
   

4. Table Row With Total

   While using remove duplicates, if you select a range of cells from a table that contains a total row then the total row is not ignored and the duplicate values are removed as well.

   
   

Invalid Selection

1. Select Single Row

   If a single row is selected.

   
   

2. Select With No Data

   If the selection does not contain any data.

   
   

3. Select One Cell

   If a single cell is selected.

   
   

4. Select Multiple Areas

   If multiple ranges are selected.