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

Question

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?

Anyone know of a way to do this without App Scripts? I got stuck trying to build a filter on the array of everything matching. — pgSystemTester
– pgSystemTester, Commented Aug 11, 2021 at 20:53

Cooper · Accepted Answer · 2021-08-11 19:40:05Z

1

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);
}

answered Aug 11, 2021 at 19:40

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Asamblea Cristiana Over a year ago

i have Error ReferenceError: length is not defined"error" (last line....)

pgSystemTester · Accepted Answer · 2021-10-08 19:51:40Z

1

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.

edited Oct 8, 2021 at 19:51

pgSystemTester

10.1k2 gold badges28 silver badges59 bronze badges

answered Oct 5, 2021 at 23:13

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

1 Comment

pgSystemTester Over a year ago

Dang. That is indeed most clever with leveraging the Row with the Match. Upvoting this answer and I'll go find another answer of yours to give an up click to (which probably won't be hard). I am going to edit your answer to include an example just because it's awesome. If for some reason you don't approve, just edit it back...

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related