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

Tags: howto, inventory management, ecommerce, google sheets, google sheets tips