1

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
1
  • @ᴡᴏʀᴅs Thanks, you captured the essence of it much better than I did.
    – jonsca
    Jul 29 '16 at 1:35
2

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]);
3
  • 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
    Jul 29 '16 at 1:16
  • 1
    Okay, edited in. As for better understanding, see Ways to learn google apps script language?
    – user79865
    Jul 29 '16 at 2:24
  • Worked great!!!
    – xyz
    Jul 29 '16 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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