Apr 25, 2014
309 Views

Kiểm tra trùng lặp trong Google Spreadsheet

Written by

Một số cách có thể áp dụng để kiểm tra trùng lặp trong bảng tính

1/ Tạo 1 cloum để nhập công thức kiểm tra

=IF(COUNTIF($F:$F;F2)=1;””;COUNTIF($F:$F;F2))

Thay đổi các giá trị cho phù hợp với bảng tính

2/ SCRIPT TO CHECK THE DUPLICATE ENTRIES IN THE SHEET

//=====================

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name : “Check Duplicates”,functionName : “checkDuplicates”}];
  sheet.addMenu(“Scripts”, entries);
};

function checkDuplicates() {

var sheet = SpreadsheetApp.getActiveSheet();

var dataRange = sheet.getDataRange();

var data = dataRange.getValues();

var numRows = data.length;

var numColumns = data[0].length;

var formats = [];

var values = [];

for (var i = 0; i < numRows; i++) {

formats[i] = [];

for (var j = 0; j < numColumns; j++) {

formats[i][j] = ‘WHITE’;

if (data[i][j] != ) {

values.push([data[i][j], i, j]);

}

}

}

var numValues = values.length;

for (var k = 0 ; k < numValues  1; k++) {

if (formats[values[k][1]][values[k][2]] == ‘WHITE’) {

for (var l = k + 1; l < numValues; l++) {

if (values[k][0] == values[l][0]) {

formats[values[k][1]][values[k][2]] = ‘RED’;

formats[values[l][1]][values[l][2]] = ‘RED’;

}

}

}

}

dataRange.setBackgroundColors(formats);

}


//=====================

3/ check Duplicates in any Range (any Row or any Column or any Range)

//================================

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name : “Check Duplicates”,functionName : “checkDuplicates”}];
  sheet.addMenu(“Scripts”, entries);
};
function checkDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(“A:A”); // Set Any Range
  // “A:A” is for Column A
  // And if you want to check duplicates for whole sheet then try this:
  // var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;
  var numColumns = data[0].length;
  var formats = [];
  var values = [];
  for (var i = 0; i < numRows; i++) {
    formats[i] = [];
    for (var j = 0; j < numColumns; j++) {
      formats[i][j] = ‘WHITE’;
      if (data[i][j] != ”) {
        values.push([data[i][j], i, j]);
      }
    }
  }
  var numValues = values.length;
  for (var k = 0 ; k < numValues – 1; k++) {
    if (formats[values[k][1]][values[k][2]] == ‘WHITE’) {
      for (var l = k + 1; l < numValues; l++) {
        if (values[k][0] == values[l][0]) {
          formats[values[k][1]][values[k][2]] = ‘RED’;
          formats[values[l][1]][values[l][2]] = ‘RED’;
        }
      }
    }
  }
  dataRange.setBackgroundColors(formats);
}

//================================

 

4/ Counting Duplicates to find most common values

I have the following formula in cell E1:
=query(A:C;”select B,count(A) where B<>” group by B label count(A) ‘Count’ “;1)

And if you want to use the above formula in another sheet, then try the following formula:
=query(‘Sheet2’!A:C;”select B,count(A) where B<>” group by B label count(A) ‘Count’ “;1)
And if you want it to sort it, so that Supervisor appearing most number of times should be showed up, then try this:
=query(A:C;”select B,count(A) where B<>” group by B order by count(A) desc label count(A) ‘Count’ “;1)

 

Article Tags:
·
Article Categories:
IT & Network
    http://linholiver.com

    https://linholiver.com/diary/about/