Remove duplicates using the Excel JavaScript API
This article provides a code sample that removes duplicate entries in a range using the Excel JavaScript API. For the complete list of properties and methods that the Range
object supports, see Excel.Range class.
Remove rows with duplicate entries
The Range.removeDuplicates method removes rows with duplicate entries in the specified columns. The method goes through each row in the range from the lowest-valued index to the highest-valued index in the range (from top to bottom). A row is deleted if a value in its specified column or columns appeared earlier in the range. Rows in the range below the deleted row are shifted up. removeDuplicates
does not affect the position of cells outside of the range.
removeDuplicates
takes in a number[]
representing the column indices which are checked for duplicates. This array is zero-based and relative to the range, not the worksheet. The method also takes in a boolean parameter that specifies whether the first row is a header. When true
, the top row is ignored when considering duplicates. The removeDuplicates
method returns a RemoveDuplicatesResult
object that specifies the number of rows removed and the number of unique rows remaining.
When using a range's removeDuplicates
method, keep the following in mind.
removeDuplicates
considers cell values, not function results. If two different functions evaluate to the same result, the cell values are not considered duplicates.- Empty cells are not ignored by
removeDuplicates
. The value of an empty cell is treated like any other value. This means empty rows contained within in the range will be included in theRemoveDuplicatesResult
.
The following code sample shows the removal of entries with duplicate values in the first column.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let range = sheet.getRange("B2:D11");
let deleteResult = range.removeDuplicates([0],true);
deleteResult.load();
await context.sync();
console.log(deleteResult.removed + " entries with duplicate names removed.");
console.log(deleteResult.uniqueRemaining + " entries with unique names remain in the range.");
});
Data before duplicate entries are removed
Data after duplicate entries are removed
See also
Office Add-ins