0

enter image description here

I have a database with thousands of rows and want to remove duplicates but also keep records from earlier in certain conditions. Here is my problem:

Column A will have unique numbers for referals in other forumlas in my Sheet, call this ID of the row.

Column B is the one I want to search for duplicates in.

Column C has a "date"

Column D has a "state"

Column E has a "comment"

Column F has an "Internal state"

  • If duplicate is found in Column B and "Date" and "State" hasnt changed the last row should be deleted.
  • If "Date" and/or "State" has changed i will update the Column C and D in the first entry, then delete the duplicate row. In this case I also want to remove the "Internal State", but only if the "Internal state" has a spesific state, lets say abc.

Here is the script i have found, but dont know how to edit to get the result i want:

enter image description here

5
  • 1
    Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Feb 26, 2021 at 21:15
  • Welcome Morten. Would you please explain what you are expecting by way of an answer. Are you expecting someone to write an entire solution, or to correct some code you have partially developed, or to provide some general guidance so that you can develop your own code, or something altogether different?
    – Tedinoz
    Feb 27, 2021 at 4:09
  • I have no very little knowledge about javascript, but have found a script to remove duplicates. the problem is that if i write something i Column E og change status it isnt a dupliucate anymore, and i dont know how to keep/merge records.
    – Morten
    Mar 3, 2021 at 14:45
  • The reason that your script isn't working is that it is designed to detect exact duplicate rows: Tutorial: Removing Duplicate Rows in a Spreadsheet. None of your rows are exact duplicates; you might think that row#6 is a duplicate of row#2 but no... row#6 has a unique ID of 5 (compared to Row#2 ID=1). In other words, this script doesn't even begin to address your needs. We need to find something more relevant.
    – Tedinoz
    May 26, 2021 at 12:27
  • This question has been bumped by Community User 8 times.
    – Rubén
    Oct 15 at 21:58

1 Answer 1

0

You want to find duplicate values in Column B and delete the record if the corresponding Date and State are identical. If a duplicate exists but either the date and/or state has changed then you want to update the original record for those changed values. In the event of a duplicate with an Internal State of, say, "abc", you also want to delete the Internal State from the original record.

You have a script that "Removes duplicate rows from the current sheet" however this uses the join method which is designed to detect exact duplicates of rows. This is inappropriate for your requirement since each of your records has a unique ID. However there are elements of this script - specifically the looping of row and newData, and newData.push(row) that can be retained.

In this answer, the deduped/updated output is copied to a new sheet; the source sheet remains intact.

/**
 * Finds duplicate values in Column B
 * deletes duplicate if date and state are identical
 * updates date and state if either has changed plus also
 * deletes Internal state if value equals watch value
 */
function wa15127902() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheetname = "Source"
  var sourcesheet = ss.getSheetByName(sourcesheetname)
  var targetsheetname = "Target"
  var targetsheet = ss.getSheetByName(targetsheetname)
  var watchInternalState = "abc"
  var data = sourcesheet.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 on Column B
        duplicate = true;
        // now test date and/or state for change
        // format dates for comparison
        var rowdate = Utilities.formatDate(row[2], "GMT","yyyy-MM-dd'T'HH:mm:ss'Z'");
        var newdate = Utilities.formatDate(newData[j][2], "GMT","yyyy-MM-dd'T'HH:mm:ss'Z'");

        if (rowdate === newdate && row[3] == newData[j][3]){
          // both the date or the state are identical
          // so this is an exact duplicate
          // do nothing
        }
        else{
          // either the date or state has changed
          // so update both
          newData[j][3] = row[3];
          newData[j][2] = row[2];
          if(newData[j][5] == watchInternalState){
            // the internal state has the specific value
            // remove the internal state
            newData[j][5] = "";
          }
        }
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  targetsheet.clearContents();
  targetsheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Source data

source data


Updated data

Target data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.