[]
DEMOS DOCS API BLOG SUPPORT PRICING DOWNLOAD
(Showing Draft Content)

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:

Spread WinForms documentation figure for Remove Duplicates from Range presents The following image shows the data before and after it has been modified using code:; the example references RemoveDuplicates for this developer task.

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.

Spread WinForms documentation figure for Remove Duplicates from Range presents You can enable the built-in remove duplicates dialog box using the RemoveDuplicates method of the BuiltinDialogs class at run-time; the example references RemoveDuplicates for this developer task.

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.

Spread WinForms documentation figure for Remove Duplicates from Range presents Spread for WinForms designer provides the "Remove Duplicates" ribbon button under the "Data" > "Data Tools" tab group; the example references RemoveDuplicates for this developer task.

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

Spread WinForms animated sequence for Remove Duplicates from Range presents The following GIF illustrates the removal of duplicates data from the selected range of data; the example references RemoveDuplicates for this developer task.

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

Spread WinForms documentation figure for Remove Duplicates from Range presents You can also choose to ignore the first row by selecting the "My Data has Headers" checkbox option; the example references RemoveDuplicates for this developer task.

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".


    Spread WinForms animated sequence for Remove Duplicates from Range presents the Remove Duplicates from Range workflow; the example references RemoveDuplicates for this developer task.

  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.


    Spread WinForms animated sequence for Remove Duplicates from Range presents the Remove Duplicates from Range workflow; the example references RemoveDuplicates for this developer task.

  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.

    Spread WinForms animated sequence for Remove Duplicates from Range presents 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; the example references RemoveDuplicates for this developer task.

  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.

    Spread WinForms animated sequence for Remove Duplicates from Range presents 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...; the example references RemoveDuplicates for this developer task.

Invalid Selection

  1. Select Single Row

    If a single row is selected.

    Spread WinForms documentation figure for Remove Duplicates from Range presents If a single row is selected; the example references RemoveDuplicates for this developer task.

  2. Select With No Data

    If the selection does not contain any data.

    Spread WinForms documentation figure for Remove Duplicates from Range presents If the selection does not contain any data; the example references RemoveDuplicates for this developer task.

  3. Select One Cell

    If a single cell is selected.

    Spread WinForms documentation figure for Remove Duplicates from Range presents If a single cell is selected; the example references RemoveDuplicates for this developer task.

  4. Select Multiple Areas

    If multiple ranges are selected.

    Spread WinForms documentation figure for Remove Duplicates from Range presents If multiple ranges are selected; the example references RemoveDuplicates for this developer task.