Google Sheets – Compare Two Lists and Find Non-Duplicated Names

filterformulasgoogle sheetsgoogle-sheets-arrayformula

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    |          |          |
+----+----------+----------+----------+

Best Answer

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)