Forum Discussion

LeandroCampacci's avatar
LeandroCampacci
Copper Contributor
Mar 14, 2023
Solved

Excel: Script to remove duplicates on a table

Hello!

 

I have a script in an Excel table that organizes values ​​in descending order and removes duplicates from a table ("Table1"). When I recorded the scripts, or followed the steps manually, the table ("Table1") is automatically adjusted to the last valid value filled in.

 

But when I run the script, duplicate values ​​are removed and the table remains with blank/empty rows.

 

Here is my script:



function main(workbook: ExcelScript.Workbook) {
	let tabela1 = workbook.getTable("Tabela1");
	// Custom sort on table tabela1
	tabela1.getSort().apply([{key: 1, ascending: false}], false);
	let selectedSheet = workbook.getActiveWorksheet();
	// Remove duplicates from range A1:E105 on selectedSheet
	selectedSheet.getRange("A:E").removeDuplicates([0,1,3,4], true);
}

 

And here is an image of how the table looks after script run:

 

 

excel
Office Scripts
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 14, 2023

    LeandroCampacci 

    Perhaps like

    function main(workbook: ExcelScript.Workbook) {
    let tabela1 = workbook.getTable("Tabela1");
    // Custom sort on table tabela1
    tabela1.getSort().apply([{ key: 1, ascending: false }], false);
    let selectedSheet = workbook.getActiveWorksheet();
    tabela1.getRange().removeDuplicates([0, 1, 3, 4], true);
    const rows = tabela1.getRowCount()
    for (let i = rows; i>0; i--)
        {
        if (tabela1.getColumn(1).getRange().getValues()[i][0] === '')
            tabela1.deleteRowsAt(i-1)
        }
}

1 Reply

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 14, 2023

    LeandroCampacci 

    Perhaps like

    function main(workbook: ExcelScript.Workbook) {
    let tabela1 = workbook.getTable("Tabela1");
    // Custom sort on table tabela1
    tabela1.getSort().apply([{ key: 1, ascending: false }], false);
    let selectedSheet = workbook.getActiveWorksheet();
    tabela1.getRange().removeDuplicates([0, 1, 3, 4], true);
    const rows = tabela1.getRowCount()
    for (let i = rows; i>0; i--)
        {
        if (tabela1.getColumn(1).getRange().getValues()[i][0] === '')
            tabela1.deleteRowsAt(i-1)
        }
}

Resources