1

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
1

=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"))

0
  • 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
-1

Click here to see a demo.

Explanation:

  • G and H shows all the possible combinations of C and D;
  • "I" tell us which combination in G and H are not present in A and B;
  • E2 queries G through I and returns the rows where "I" are TRUE.

Edit

Click here for a single (E2), improved and compact formula.

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.