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

Question

I have three interrelated google sheet spreadsheets. In the worksheet "Input data" names and different roles are defined. 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),"")

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:

=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. 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

why not build a simple sample sheet with 3 sheets and show us what you want to happen... your formula is useless to us because it looks like you have undefined functions. — CodeCamper, Dec 02 '19 at 14:25
These function names are in German. Took me a second to realize. And yes, it will be much easier to troubleshoot this question with sample data. I also think Apps Script may be easier than using built-in formulas for this problem. — Adam Stevenson, Dec 02 '19 at 14:49
Hello, could you please provide more information about your data and the function you have used? Thanks a lot! — carlesgg97, Dec 02 '19 at 16:23
I took pictures of an example sheet and edited the question a bit! Could you please help me now? @carlesgg97 — greenster10, Dec 05 '19 at 14:50
I took pictures of an example sheet and edited the question a bit! Could you please help me now? @CodeCamper — greenster10, Dec 05 '19 at 14:51
I took pictures of an example sheet and edited the question a bit! Could you please help me now? @AdamStevenson — greenster10, Dec 05 '19 at 14:51
@greenster10 Images of screenshots are no substitute for the actual spreadsheet. It's no accident that 4 days passed without further comment - users are waiting for you to share a copy of your spreadsheet. It is really in your best interests. On a tangent, the images that you have inserted in your question are forcing text to wrap such that the question is now unintelligible. I suggest that you review your question, and the positioning of the text and images. — Tedinoz, Dec 06 '19 at 00:59
This is question #59140197. Is your other question (#59135936) a duplicate of this question? — Tedinoz, Dec 06 '19 at 01:03
example sheet with explanation: https://docs.google.com/spreadsheets/d/1OU_95Lhf6p0ju2TLlz8xmTegHpzTYu4DW0_X57mObBc/edit#gid=1763280488 — greenster10, Dec 06 '19 at 10:11
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! — greenster10, Dec 06 '19 at 10:20

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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

In the Google Sheets UI, select Tools > Script editor to open the script bound to the sheet in the Apps Script editor.

Write the macro function. Macro functions should take no arguments and return no values.

Edit your script manifest to create the macro and link it to the macro function. Assign it a unique keyboard shortcut and name.

Save the script project. The macro is then available for use in the sheet.

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

In the Google Sheets UI, select Tools > Macros > Import.

Select a function form the list presented and then click Add function.

Select clear to close the dialog.

Select Tools > Macros > Manage macros.

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.

Click Update to save the macro configuration.

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

Thank you man! I will try it in 2hours and tell you if it worked! :) — greenster10, Dec 06 '19 at 12:46
it deletes the row over the row, that should be deleted, so the wrong row. Where is the mistake?@ale13 — greenster10, Dec 06 '19 at 14:37
Hello @greenster10, depending on the sheet you are using, you might need to adjust the parameters. The code works for the two sample sheets you provided (C3:C because the 'Input data' sheet has headers from C1:C2, and B3:B because the 'Assignment' sheet has headers from B1:B2). So I'm asking you, did you modify any of the ranges from the code snippet I provided? If so, you should adapt the number in this line of code (`s2.deleteRow(i+3);`) accordingly. — ale13, Dec 06 '19 at 14:55
in the original sheet it is C3:C in Assignment and C4:C in Input data, so how has ```(s2.deleteRow(i+3);)``` to look? (i+4)? — greenster10, Dec 06 '19 at 14:59
what do I have to insert if I want to just delete the contents of the row and not the whole row itself? so that the row is empty respectively a --- is in the whole row? — greenster10, Dec 06 '19 at 16:03
https://stackoverflow.com/questions/59900660/creating-a-data-history-with-excel-vba-using-lastrow-time-stamp-and-workbook-sh could you please help me here? — greenster10, Jan 25 '20 at 09:38

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

1 Answers1