Google-sheets – Output the entries of one column that are not in another, preserving their row positions

google sheetsgoogle-apps-script

I want for the entries from column A that are not in column C to show up in column E, in the same row as they were originally.

I obtained code for a matching function from Stack Overflow, stated below. It also looks at columns A and C, and the ones from column A that are not in column C will show up in E. However, they are shown consecutively, not on the same row. 

function onOpen() {
  SpreadsheetApp.getUi().createMenu("CD Report")
  .addItem("Agreement Report", "agreementReport")
  .addToUi();
}

function agreementReport(){
  var as = SpreadsheetApp.getActive();
  var sheet = as.getSheetByName("15-16 1:1 agreement");

  var handedIn = sheet.getSheetValues(2, 1, sheet.getLastRow(), 1);
  var stuNames = sheet.getSheetValues(2, 3, sheet.getLastRow(), 1);
  var list = []; 

  for (i in stuNames){
    var curName = stuNames[i][0];
    var exists = false;

    for (j in handedIn){
      var curCheck = handedIn[j][0];
      if (curCheck == curName){
        exists = true;
        break;
      }
    } // end for j
    if (exists == false){
      list.push([curName]);
    } 
      } // end for i

      sheet.getRange(2, 5, list.length, 1).setValues(list);

    } // end agreementReport

Best Answer

You don't need a script for this version of the task: one formula in the cell E1 does the job.

=arrayformula(if(isna(match(A:A, C:C, 0)), A:A, iferror(A:A/0)))

This says: if the value of A is not in C (that is, match returns #N/A!), then use the value from A. Otherwise, leave blank (this is what iferror(A:A/0) does).

The script you have can also be used with the following modification: replace the lines

  if (exists == false){
      list.push([curName]);
    }

by 

  list.push([exists ? "" : curName]);
Related Topic