Google Sheets - Find Values in One Column that are Missing in Another Column
How to compare columns in Google Sheets and highlight cell values that are in one column but missing in another column.
A small business maintains their staff roster in a simple Google Sheet - the column A of the sheet contains a list of all employee names and column B contains a list of employees who have been assigned to a project.
The immediate task is to identify staff members who are part of the organization but have not been assigned any project yet. In other words, the manager needs to figure out all employee names from column A who are not preset in column B.
There are two ways to solve this problem - visually and through formulas.
The first option would be to highlight cells in column A that are missing in column B.
Inside the Google Sheet, go to the Format menu and choose conditional formatting. Here select
A2:A for the range field, choose
Custom Formula from the
Format Cells If dropdown and paste the formula:
COUNTIF function will essentially count the occurrence of each item in Column A against the range of cells in Column B. If the count for an item in Column A is 0, it means that the cell value is not present in column B and the cell is highlighted with a different background color.
The next approach uses Google Sheet formulas to create a list of items that are in Column A but missing in Column B.
We’ll make use of the
FILTER function that, as the name suggests, returns only a filtered version of a range that meets a specific criteria. In our case, the criteria is similar to the one that we used in the visual formatting section.
Go to column C (or any blank column) and enter this formula in the first empty cell.
MATCH function returns the position of items in Column A in the range associated with Column B and it returns #N/A if the values is not found. When the result is used with
ISNA, it returns true only when the match is not found.
SQL geeks may also use the Google Query Language, we are used it with D3.js visualization, to print the list of names that are in Column B but not in Column B.
=QUERY(A2:A, "SELECT A WHERE A <> '' AND NOT A MATCHES '"&TEXTJOIN("|",TRUE,B2:B)&"' ORDER BY A")
matches operator in the where clause does a regex comparison and the
order by clause in the query will automatically sort the output alphabetically.
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.