-1

I am looking for a piece of advice from someone who is familiar with scripts for google sheets The application is grading a written test/exam for a cohort of students There are two tables:

  • the first one contains the answers written by students, and additionally includes their names (for identification)
  • the second one contains the list of all the students in the cohort

Problem 1: join the tables 1 and 2 with outer join. The output is a table that contains all the students from the cohort, and for those who did take the test it contains the answers they provided

Problem 2: create a new tab that contains the difference between the tabs 2 and 1, i.e. the list of all the students that did not take the test

I know how to do this in python/pandas, but I don't have much experience with google sheets, so any help on the implementation would be appreciated. Thank you!

khorms
  • 13
  • 1

1 Answers1

0

I have 3 formulas you could try.

Here is some mockup data that represents what I think you have (for convenience, I've put it on one tab):

enter image description here

I've added student names in Col A, deliberately not alphabetical. Test results in Cols C and D for those who have taken the test.

The formula to get all students, and their result (if taken) is in cell F2 (yellow):

=sort(arrayformula({A2:A,if(A2:A<>"",iferror(vlookup(A2:A,{D:D,C:C},2,false),),)}),1,true)

It uses:

ARRAYFORMULA to cascade the result down the table.

{D:D,C:C} is an array that gets column D, then C for the VLOOKUP range. You can alter this depending on your source data.

VLOOKUP to get the test result based on the list of students.

A2:A<>"" to do the test as long as there is a student name in Col A.

IFERROR to ignore results where student name is not found.

SORT to order the results by student name, alphabetically.

The gaps in Col G are obviously those who did not test.

If you want definitive lists of those who did and did not test, then the formulas in I2 and L2 give you that. They are basically the formula from F2 with a QUERY wrapped around.

I2 (green):

=query(arrayformula({A2:A,if(A2:A<>"",iferror(vlookup(A2:A,{D:D,C:C},2,false),),)}),"where Col2 is not null order by lower(Col1)",0)

L2 (red):

=query(arrayformula({A2:A,if(A2:A<>"",iferror(vlookup(A2:A,{D:D,C:C},2,false),),)}),"select Col1 where Col2 is null and Col1 is not null order by lower(Col1)",0)

Aresvik
  • 4,484
  • 1
  • 5
  • 18
  • Since you have mentioned joins I am guessing you are familiar with SQL. Sheets has a QUERY() function modeled after SQL although it is not a full implementation and does not directly support JOINs. If you are interested in trying a QUERY() approach let me know, I have developed a customer function which provides INNER, LEFT, RIGHT and FULL JOINs I would be happy to share with you here. – JohnA May 12 '21 at 10:44