Hey, friends today I will teach you How to Find Values in One Column that are Missing in Another Column. A small business keeps their employee roster in a simple Google Sheet, with column A listing all employee names and column B listing employees who have been assigned to a project.
so let get started with today Code snippets. Getting different problems is altogether gives a very different experience. today the Code snippets I am going to share with you is How to Find Values in One Column that are Missing in Another Column.
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.
You might also like our trending code snippets
- Delete Blank Rows from Tables in your Google Documents
- Replace Text in Header and Footer -Google Documents
- Use PayPal Subscriptions API with Node.js
- Create React App with Multiple Entry Points
There are two ways to solve this problem – visually and through formulas.
How to compare columns in Google Sheets and highlight cell values that are in one column but missing in another column.
Using Visual Formatting
The first option is to highlight cells in column A that are not present in column B.
Go to the Format menu within Google Sheet and select conditional formatting. Choose A2:A for the range field and Custom Formula from the Format Cells menu. If you use a dropdown menu and copy and paste the formula:
=COUNTIF(B$2:B, A2)=0The COUNTIF function will essentially count the number of times each item in Column A appears in relation to the range of cells in Column B. If an item in Column A has a count of 0, it means that the cell value is not present in Column B, and the cell is highlighted with a different background colour.
Find Missing Items in Another Column
The following method employs Google Sheet formulas to generate a list of items that are present in Column A but not in Column B.
We’ll use the FILTER function, which, as the name implies, returns only a filtered version of a range that meets a certain set of criteria. In our case, the criteria are similar to those used in the section on visual formatting.
Go to column C (or any blank column) and enter this formula in the first empty cell.
=FILTER(A2:A,ISNA(MATCH(A2:A,B2:B,0)))
The MATCH function returns the position of items in Column A in the range associated with Column B, or #N/A if no values are found. When used with ISNA, the result returns true only when no match is found.
Using Google Query Language
SQL experts can also use the Google Query Language, which we used in conjunction with the D3.js visualisation, to print a 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")
The where clause’s matches operator performs a regex comparison, and the query’s order by clause sorts the output alphabetically.