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")

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;
    } // end for j
    if (exists == false){
      } // end for i

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

    } // end agreementReport
  • @ᴡᴏʀᴅs Thanks, you captured the essence of it much better than I did.
    – jonsca
    Commented Jul 29, 2016 at 1:35

1 Answer 1


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([exists ? "" : curName]);
  • WORDS, thank you for the code, I will use it. But do you know how to alter the function aswell, this will help me better understand how to write scripts. Thank again
    – xyz
    Commented Jul 29, 2016 at 1:16
  • 1
    Okay, edited in. As for better understanding, see Ways to learn google apps script language?
    – user79865
    Commented Jul 29, 2016 at 2:24
  • Worked great!!!
    – xyz
    Commented Jul 29, 2016 at 2:51

Your Answer

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

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