CODEWITHSUNDEEP
javascriptgoogle-apps-script
Asamblea Cristiana
Asamblea Cristiana

Reputation: 11

GOOGLE APPS SCRIPT:remove Duplicate row ( compare col C and D with rest rows)

i have a spreadsheet with 7 columns, i want delete duplicate rows. But i need compare col C and D values with the others rows. if there are another row with the same value (col C and D) just deletes it, i tried use the "unique function", but this compare all cols values.

colA colB colC colD colE colF colG
row1 short walter **white size2** 2019 enero 0002
row2 pants Carlos green size3 2019 julio 0003
row3 boxer Felipe **white size2** 2020 marzo 0005
row4 tshirt walter **yellow size1 ** 2019 junio 0002
row5 sock Martin **yellow size1 ** 2021 mayo 0005

In the example before (just comparing Col C and col D) i need delete row 3 (the same value in row1) and row 5 (the same valueS row 4)

I need google script code to do that.

any can help me?

Upvotes: 0

Views: 601

Answers (2)

TheMaster
TheMaster

Reputation: 50452

As a formula,

=FILTER(A1:G5,MATCH(C1:C5&D1:D5,C1:C5&D1:D5,0)=ROW(C1:C5))
  • Create a joint array of col C and col D using &
  • MATCH to match the array among itself to get a matching row number
  • Compare the MATCH result with actual ROW number. This generates a TRUE/FALSE array of duplicates
  • FILTER the entire table using the duplicates array above.

Example file here.

Upvotes: 1

Cooper
Cooper

Reputation: 64062

function deleteDupes() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn());
  const vs = rg.getDisplayValues();
  let d = 0;
  let uA = [];
  let oA = [];
  vs.forEach((r,i) =>  {
    let t = String(r[2]+r[4]);
    if(!~uA.indexOf(t) {
      uA.push(t);
      oA.push(r);
    }
  });
  rg.clearContent();
  sh.getRange(2,1,oA.length,oA[0],length).setValues(oA);
}

Upvotes: 1

Related Questions