Copy Duplicates and Move them to another sheet

Question

Dataset Here's another post that is the same situation: Link to the post

I'm trying to find a script that goes through column A which is a concat formula of columns 1-5. Then I want it to identify which are duplicates and copies the whole row into another worksheet. I've modified the code within that post as it's pretty much what I'm looking for but it crashes my whole excel. I have about 80,000 lines of data which is the problem and so far my excel sheets are all formula based so it takes forever to load and I'm trying to use less formulas and more automation.

Thank you!

Tried the code within the post which works but freezes entire excel and all Microsoft applications.

Answer 1

• You don't need a helper column with Concat formula. It can be done with code (VBA or OfficeScript)

function main(workbook: ExcelScript.Workbook) {
    const Sheet1 = workbook.getWorksheet("Sheet1");  // modify as needed
    const Sheet2 = workbook.getWorksheet("Sheet2");
    const dataRng = Sheet1.getRange("A1").getSurroundingRegion()
    // console.log(dataRng.getAddress())
    const dataVals = dataRng.getValues();
    // console.log(dataVals)
    // Merge rows into strings (same as Concat formula)
    const mergedRows: string[] = [];
    for (let i = 0; i < dataVals.length; i++) {
        mergedRows.push(dataVals[i].join("|"));
    }
    // console.log(mergedRows)
    const dupVals = findDuplicateRows(mergedRows);
    const rowCnt = dupVals.length;
    const colCnt = dupVals[0].length;
    // clear the output sheet
    Sheet2.getRange().clear(ExcelScript.ClearApplyTo.all);
    // write dup. rows to sheet
    Sheet2.getRange("A1").getAbsoluteResizedRange(rowCnt, colCnt).setValues(dupVals);
}

function findDuplicateRows(mergedRows: string[]): string[][] {
    const duplicateRows: string[][] = [];
    const foundRows: Set<string> = new Set();
    for (let i = 0; i < mergedRows.length; i++) {
        const row = mergedRows[i];
        if (!foundRows.has(row)) {
            for (let j = i + 1; j < mergedRows.length; j++) {
                if (row === mergedRows[j]) {
                    duplicateRows.push(row.split("|")); 
                    foundRows.add(row);
                    break;
                }
            }
        }
    }
    return duplicateRows;
}