How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?

Sample data:

A     B          C         D            E     F
1 name  handed in  students  assignments  name  missing
2 a     1          a         1            b     2
3 b     1          b         2            b     3
4 c     1          c         3            c     2
5 a     2
6 c     3
7 a     3

Say I am a teacher so:

• C has a list of all students enrolled in my class;
• D has a list of all assignments;
• Each and every student in C must hand in all of the assignments listed in D;
• A and B I keep feeding with student name and assignment handed in.

Now, at the end of the day, I wanna know who are the bad students who have not handed in all of their assignments.

I need a formula that lists in E and F the student name and the assignments they were supposed to have handed in.

In my sample data student "a" is a good student, student "b" missed out assignments 2 and 3 and student "c" missed out assignment 2.

• let's say you are a teacher, are columns C and D somehow generated or they are just manually typed – user0 Feb 19 at 18:02
• Manually typed but must be dynamic, that is, I might insert or remove data at will. – Francisco Luz Feb 19 at 18:14
• I figure we must start out finding a way of dynamically creating a list from C and D containing, in our sample, 9 item pairs. So a1, a2, a3, b1, b2, b3, c1, c2, c3. Then we can quite easily find out what pairs are not in A and B. – Francisco Luz Feb 19 at 19:15
• I see, so you actually need to calculate C, D, E, F based on A and B, right? – user0 Feb 19 at 23:19
• Yeah, from C and D you must create a list of all possible combinations. E and F gotta be the list of items from the "all possible combinations" which are not present in A and B. – Francisco Luz Feb 20 at 0:37

=ARRAYFORMULA(QUERY({QUERY({
TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)),  CHAR(9))),
TRANSPOSE(SPLIT(CONCATENATE(REPT(D2:D&CHAR(9),  COUNTA(C2:C))), CHAR(9)))},
"where Col1 is not null", 0), ARRAYFORMULA(IF(QUERY({
TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)),  CHAR(9))),
TRANSPOSE(SPLIT(CONCATENATE(REPT(D2:D&CHAR(9),  COUNTA(C2:C))), CHAR(9)))},
"select Col1 where Col1 is not null", 0)="", ,ISNA(VLOOKUP({QUERY({
TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)),  CHAR(9))),
TRANSPOSE(SPLIT(CONCATENATE(REPT(D2:D&CHAR(9),  COUNTA(C2:C))), CHAR(9)))},
"select Col1 where Col1 is not null", 0)}&{QUERY({
TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)),  CHAR(9))),
TRANSPOSE(SPLIT(CONCATENATE(REPT(D2:D&CHAR(9),  COUNTA(C2:C))), CHAR(9)))},
"select Col2 where Col2 is not null", 0)}, {A2:A}&{B2:B}, 1, 0))))},
"select Col1, Col2 where Col3=TRUE order by Col1")) • mathematical solution:

=ARRAYFORMULA(QUERY({FILTER(D2:D, D2:D<>""),
IF(LEN( QUERY(A2:B, "select sum(B) where B is not null group by A label sum(B)''", 0)),
IF(E2:E-QUERY(A2:B, "select sum(B) where B is not null group by A label sum(B)''", 0)<0, ,
E2:E-QUERY(A2:B, "select sum(B) where B is not null group by A label sum(B)''", 0)), )},
"where Col2 is not null", 0)) • cell D2: =UNIQUE(FILTER(A2:A, A2:A<>""))
• I get an error, take a look at bit.ly/2Gyhq4t Anyhow, from your screenshot, the result in G and H are not correct. I mean, student "b" has handed in their assignment 1. – Francisco Luz Feb 20 at 2:02
• Another issue is if, for instance, I want to insert the student "d" who, at this point, has handed in none of the assignments. – Francisco Luz Feb 20 at 2:06
• B column needs to be formated as number not text. and for student d... well it acounts that you wont type in student name unless he handed over something xD – user0 Feb 20 at 2:10
• Yeap! that was it. Changed to number formatting and got the same result as yours. – Francisco Luz Feb 20 at 2:11
• its a different approach... in this scenario student "a" handed over 6 assignments but it required only 1 of him to hand over so he is 5 ahead... – user0 Feb 20 at 2:12