Removing row if it contains a duplicate cell

Question

We have a remove duplicates function that removes rows that are previously duplicated, i.e., where Row 2, comprising B1 and B2, is deleted if B1 and B2 are identical to Row 1's A1 and A2. But we want it to be such that Row 2 is deleted only if B2 is identical to A2. Here is the code we have thus far:

    function removeDuplicates() {
  const startTime = new Date();
  const newData = [];
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const numRows = data.length;
  const seen = {};

  for (var i = 0, row, key; i < numRows && (row = data[i]); i++) {
    key = JSON.stringify(row);
    if (key in seen) {
      continue;
    }
    seen[key] = true;
    newData.push(row);
  };

The function should only affect latter rows and not former rows (i.e., Row 2 should be deleted, and not Row 1).

Marcus · Accepted Answer · 2019-03-21 07:43:01Z

1

Maybe you can try a different approach like applying a formula on the spreadsheet like =UNIQUE(Sheet1!B:B) and copy the result to a new sheet. This should also be possible programmatically. See also:

EDIT: I think what you need is described in a Google Developer tutorial. https://developers.google.com/apps-script/articles/removing_duplicates See section "Variation". I have adapted this to match duplicates on the second column. I have tested this with a sample spreadsheet.

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var j in newData) {
      if(row[1] == newData[j][1]){
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

edited Mar 21, 2019 at 7:43

answered Mar 20, 2019 at 18:15

Marcus

1,18212 silver badges23 bronze badges

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

3 Comments

Ed Dev Over a year ago

Thank you, but the UNIQUE formula won't work for us because we need to retain congruence between columns. We would need to use that formula for two columns (A, which is for names, and B, which is for emails). But running the formula for A and B displaces the original matching (where A1 is the name of the person whose email is in B2).

Ed Dev Over a year ago

The UNIQUE function is also a bit unwieldy for sheets to which we are adding new entries and want to check that each new entry is not a duplicate from preceding rows.

Marcus Over a year ago

Ok, I see. Tried this myself in does not work as I thought. How about the first answer to remove-rows-based-on-duplicates-in-a-single-column-in-google-sheets? I tried this an it works for me, at least, when I insert the filter on the same spreadsheet.

Collectives™ on Stack Overflow

Removing row if it contains a duplicate cell

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related