Google Sheets Tip: Compare Two Columns and Extract Missing Values
You may run into a situation where you need to compare two spreadsheets and figure out which values exist in one but not the other.
For example, if you run an ecommerce store where your supplier provides you a spreadsheet of their inventory that's constantly changing, you may need to figure out which new products exist in the data feed that you currently don't have published in your store.
One way to figure this out is to export the current product data from your store to CSV/Excel and then compare them to your supplier's feed by SKU.
Here's a quick way you can accomplish this using Google Sheets:
Step 1. Create 2 columns in a Google Sheets spreadsheet, where Column A contains all the SKUs from your supplier's feed and Column B will be the SKUs from your store.
Step 2. Use the formula below to output the SKUs that exist in your supplier's feed but don't exist in your store:
=FILTER(A2:A, ISNA(MATCH(A2:A, B2:B, 0)))
Credit goes to this article for this formula.
Here's a sample of what this looks like in Google Sheets:
There may also be a case where you need to do the opposite and figure out which SKUs still exist in your store but no longer exist in your supplier's feed. In this case, simply reverse the column references in the formula (i.e. replace A -> B and B -> A).
- How to Reference the Current Row in Google Sheets Formulas
- How to Change CSV File Encoding to UTF-8 with Google Sheets
- How to Create a Basic Pivot Table in Google Sheets
- How to Generate QR Codes in Google Sheets
- Google Sheets Tip: Generate JSON Data from Rows and Columns
- Google Sheets Tip: Generate a Comma-Separated List of Values From a Column
- How to Display Images from URLs in Your CSV File Using Google Sheets