Google-sheets – How to remove duplicate rows based on one column, retaining the first row for each

google sheets

I want remove all row with equal date

02/03/2016  1   11  24  7   8   3   22
02/03/2016  1   11  23  7   8   3   22
03/03/2016  1   11  25  7   8   3   22
03/03/2016  1   11  25  8   7   3   22
04/03/2016  1   11  25  8   7   3   22
04/03/2016  1   11  25  7   8   3   22
04/03/2016  1   11  25  9   7   3   22
04/03/2016  1   11  25  9   8   3   22

After removal I want only

02/03/2016  1   11  24  7   8   3   22
03/03/2016  1   11  25  7   8   3   22
04/03/2016  1   11  25  8   7   3   22

Is there a way to do that?

Best Answer

So, you want to retain only the first entry for each date. One way to do it is to filter by "column A entry not equal to the one above":

=filter(A2:H100, A2:A100 <> A1:A99)

The need to shift the indices in the comparison range may be a nuisance. Here's a version that does the shift itself: 

=filter(A2:H100, A2:A100 <> offset(A2:A100, -1, 0))

However, neither of these two versions works with open-ended range like A2:H because offsetting it by -1 results in one more row. Here is a version that handles either kind of range:

=filter(A2:H, A2:H <> array_constrain(offset(A2:A, -1, 0), rows(A2:A), 1e7))

Apps Script function

This function deletes duplicate rows from the current sheet, following the same logic: delete a row if its A entry is equal to the one above it. 

function removeDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getLastRow();
  var firstColumn = sheet.getRange(1, 1, rows, 1).getValues();
  for (var i = rows; i >= 2; i--) {
    if (firstColumn[i-1][0] == firstColumn[i-2][0]) {
      sheet.deleteRow(i);
    }
  }
}
Related Topic