We can easily code one formula to find the names in a project that are not appearing in any other projects in Google Sheets.

It will be a worthy exercise when you have several projects running, and also, employees are assigned to multiple projects.

There are many approaches in Google Sheets. For example, a combination using COUNTIF and FILTER, COUNTIF and REGEXMATCH, etc.

I’m following the former one.

Do you have multiple projects and only want to assign unassigned employees from an employee list?

If so, I have already shared a tutorial, that link I will share at a later stage below (we will again use that formula with our new sample data below).

Here my goal is to find the names of employees in a project who are not appearing in any other projects.

For example, I have the following dataset in the range A1:C (please ignore E1:F).

I want to find the name of employees in a particular project, i.e., “Project A,” that does not appear in any other projects.

The formula in H3 returns that name.

If you only want to find the unassigned employees, you are required to compare the lists in B1:C with E1:F.

The formula in cell H8 does the same. Let’s start from there.

Finding Employees Who Are Not Yet Assigned in Any Projects

Before finding the names of employees in one project who are not yet assigned to any other projects, let’s go to the formula in cell H8.

The H8 formula compares the employee list E2:F11 in the project list B2:C16 and returns the unassigned names.

Formula # 1:

=sort(if(COUNTIF(B2:B&C2:C,E2:E&F2:F)=0,E2:F,))

You may have already seen this formula here – Google Sheets: Compare Two Lists and Extract the Differences.

Here there is one difference in the usage, though.

Here, I have combined the first and last names.

If you have the first name only, you should remove &C2:C and &F2:F from the formula.

Further, replace the last array in the formula, i.e., E2:F (first and last name), with E2:E (first name).

Note:-

The above formula may return several blank rows below the output. As a result, when you enter any value below cell H9 or I9, it will return #REF!

We can avoid that using Query.

=query(sort(if(COUNTIF(B2:B&C2:C,E2:E&F2:F)=0,E2:F,)),"Select * where Col1 is not null",0)

Time to code the Google Sheets formula to find the names in a project that are not appearing in any other projects.

Find Names in One Project That Are Not Appearing in Any Other Projects

Now to the test.

This time we have only one table that is in the range A1:C.

When you go through column A, you can find four projects. Pick any of the one projects.

Let’s match the employees assigned in “Project A” within other projects and return the mismatching ones.

In other words, let’s find the names in one project that are not appearing in any other projects.

We can easily code the formula by following formula # 1 above.

There are two major parts in the above formula, and let’s call them table_1_ref and table_2_ref. They are as follows.

• table_1_ref – B2:B&C2:C
• table_2_ref_1 – E2:E&F2:F
• table_2_ref_2 – E2:F.

Coding Part of the Formula

We have only one table here as we just want to find the names in one project that are not appearing in other projects.

We should virtually create two tables out of this.

• table_1_ref – All the other projects, i.e., filter(B2:B&C2:C,A2:A<>"Project A") .
• table_2_ref_1 – The specific project to test in all the other projects for distinct employees, i.e., filter(B2:B&C2:C,A2:A="Project A").
• table_2_ref_2 – Same as above, but a slightly different formula, i.e., filter(B2:C,A2:A="Project A").

In formula # 1, find the array/range references given in bulleted list one.

Replace them with the corresponding filter formulas in bulleted list two above.

Formula # 2:

=sort(if(COUNTIF(filter(B2:B&C2:C,A2:A<>"Project A"),filter(B2:B&C2:C,A2:A="Project A"))=0,filter(B2:C,A2:A="Project A"),))

The above formula in cell H3 finds the names in “Project A” that are not appearing in the other projects.

You feel free to use other project names instead of “Project A.”

Here is an alternative Formula for Google Sheets enthusiasts.

=ifna(filter(B:C,A:A="Project A",regexmatch(B:B&C:C,"^"&textjoin("$|^",true,FILTER(B:B&C:C,A:A<>"Project A"))&"$")=FALSE),"No match!")

Resources

• How to Compare Two Sheets in Google Sheets for Mismatch.
• Compare Two Google Sheets Cell by Cell and Highlight.
• Filter Based on a List in Another Tab in Google Sheets.
• How to Compare Two Columns for Matching Values in Google Sheets.
• Compare Two Sets of Multi-Column Data for Differences in Google Sheets.
• Google Sheets: How to Compare Two Tables and Remove Duplicates.
• Highlight Matches or Differences in Two Lists in Google Sheets.
• How to Compare Comma-Separated Values in Google Sheets.
• Compare Two Rows and Find Matches in Google Sheets.
• Multiple CONTAINS in WHERE Clause in Google Sheets Query.