Find the Names That Are Not Appearing in Any Other Projects in Google Sheets

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).

Filter Names That Are Not Appearing in Any Other Projects - Example

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_refB2:B&C2:C
  • table_2_ref_1E2:E&F2:F
  • table_2_ref_2E2: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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.