CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsvlookupgoogle-sheets-formula
greenster10
greenster10

Reputation: 79

Is it possible to write a script for this Vlookup error?

I have three interrelated google sheet spreadsheets. In the worksheet "Input data" names and different roles are defined. Input Data In the table sheet "Assignment" these specific names are assigned with values: I use the following formula on Location: =IF(C2<>"", VLOOKUP(C2,'Input Data'!C$3:D$7,2,FALSE),"") Assignment

In the table sheet "Evaluation" an evaluation takes place. The problem is that if a name (for example: Green,John) is deleted from "Input Data" but is still in "Assignment", the following error occurs on the "Evaluation" worksheet for this formula: Project Overview Evaluation 

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF((IF($B$2="dontcare",1,REGEXMATCH(Assignment!$E$3:$E$577,$B$2 &"*")))*(IF($B$3="dontcare",1,(Assignment!$E$3:$E$577=$B$3)))*(IF($B$4="dontcare",1,(Assignment!$D$3:$D$577=$B$4)))*(IF($B$5="dontcare",1,(Assignment!$F$3:$F$577=$B$5)))*(IF($B$6="dontcare",1,(Assignment!$B$3:$B$577=$B$6))),(Assignment!S$3:S$577)))), 1, 1)

Error: When evaluating VLOOKUP, the value "Green, John" was not found. Error Is it possible to avoid this error with a macro that deletes Names from assignment sheet that are not in the Input data sheet? Do you have any ideas for a code? So my only question is, how to write a script that deletes the complete lines in the assignment list, if a line occurs with a name like Green, John, that is not in the input data list. short version: Example! Assignment sheet: line 7 Green, John -> check if Green, John is in sheet Input Data in row C! If Green, John is not in Input Data row C -> delete line 7 Green, John in Assignment! .... That is basically my question! example sheet with further explanation: https://docs.google.com/spreadsheets/d/1OU_95Lhf6p0ju2TLlz8xmTegHpzTYu4DW0_X57mObBc/edit#gid=1763280488

Upvotes: 0

Views: 153

Answers (1)

ale13
ale13

Reputation: 6052

A solution for your problem using Apps Script is presented here. The algorithm works by looping through both of the columns, comparing the values from each one of them and then removing the unwanted rows.

function checkCol() {
  
  
  var s1 = SpreadsheetApp.getActive().getSheetByName("Input data");
  var col1 = s1.getRange("C3:C").getValues();
  
  var s2 = SpreadsheetApp.getActive().getSheetByName("Assignment");
  var col2 = s2.getRange("B3:B").getValues();
  
  for (var i=col2.length-1; i>=0; i--) {
    var found = false;
    for (var j=0; j<col1.length; j++) {
       if (col1[j][0] == col2[i][0]) {
         found = true;
         break;
       }
    }
    
    if (!found) {
      s2.deleteRow(i+3);
    }
  }
}

If you want to use this function as a macro for your Sheet, you should do this (according to the Google Sheets Macros documentation

Creating macros in Apps Script

  1. In the Google Sheets UI, select Tools > Script editor to open the script bound to the sheet in the Apps Script editor.
  2. Write the macro function. Macro functions should take no arguments and return no values.
  3. Edit your script manifest to create the macro and link it to the macro function. Assign it a unique keyboard shortcut and name.
  4. Save the script project. The macro is then available for use in the sheet.
  5. Test the macro function in the sheet to verify that functions as intended.

When importing the function as a macro, you should follow these steps:

Importing functions as macros

  1. In the Google Sheets UI, select Tools > Macros > Import.
  2. Select a function form the list presented and then click Add function.
  3. Select clear to close the dialog.
  4. Select Tools > Macros > Manage macros.
  5. Locate the function you just imported in the list. Assign a unique keyboard shortcut to the macro. You can also change the macro name here; the name defaults to the name of the function.
  6. Click Update to save the macro configuration.

You can read more about Apps Script and the main methods used in this solution here:

  1. Class Sheet;

  2. Google Sheets Macros.

Upvotes: 1

Related Questions