1

I have one list of total available people and the second list of people who have been assigned. I would like to auto-populate a third list of the people (from the first list) who have not been assigned (list B). Basically whichever names from column A that are not used in Column B would show up in Column C.

+----+----------+----------+----------+
|    | A        | B        | C        |
+----+----------+----------+----------+
| 1  |   All    | Assigned |   Free   |
+----+----------+----------+----------+
| 2  | AJ       | AJ       | Dayna    |
+----+----------+----------+----------+
| 3  | Dayna    | Leah     | Kristina |
+----+----------+----------+----------+
| 4  | Kristina | Mag      | Mai      |
+----+----------+----------+----------+
| 5  | Leah     | Milla    | Sarah    |
+----+----------+----------+----------+
| 6  | Mag      | Mimi     |          |
+----+----------+----------+----------+
| 7  | Mai      | Oksana   |          |
+----+----------+----------+----------+
| 8  | Milla    | Richelle |          |
+----+----------+----------+----------+
| 9  | Mimi     |          |          |
+----+----------+----------+----------+
| 10 | Oksana   |          |          |
+----+----------+----------+----------+
| 11 | Richelle |          |          |
+----+----------+----------+----------+
| 12 | Sarah    |          |          |
+----+----------+----------+----------+
0

C2: =ARRAYFORMULA(FILTER(A2:B; ISERROR(MATCH(A2:A; B2:B; 0))))


bonus knowledge:

  • to achieve the opposite:
    =FILTER(A2:A; REGEXMATCH(A2:A; "^"&JOIN("|"; FILTER(B2:B; LEN(B2:B)))&"$"))
  • to avoid #REF! error if D column exists:
    =ARRAY_CONSTRAIN(FILTER(A2:B; ISERROR(MATCH(A2:A; B2:B; 0))); 1000; 1)
  • 1
    Awesome thank you. I think this is will be exactly what I need. – Allister Jul 12 '18 at 2:17

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.