Sheets Tip 322: Comparing two columns


Brought to you by:


Hi Reader,

Welcome to the Google Sheets Tips newsletter #322, your Monday morning espresso, in spreadsheet form.

After last week's elegant FILTER formula, I have another elegant FILTER formula for you. How lucky are you!?! ;)

That wasn't by design though. I thought I had an idea for this week until I checked the newsletter ​archives​ and realized I'd covered it back in February 2024 as tip 290.

So I searched my email correspondence related to that tip and found a goldmine of ideas. So today I'm sharing one of those with you.

But first, a little story...

When began my accounting career in 2006, one of the first things my boss had me do was download an Excel Add In that added a suite of powerful utility tools to Excel. Things like removing duplicates, merging columns, doing text transformations. All the tedious stuff that is tricky to do with formulas and can eat up all your time.

Well, this tool simplified all those types of tasks by reducing them to the click of a few buttons. It was indispensable part of my spreadsheet toolkit.

Fast forward to today, and I use ​Power Tools in Google Sheets​ to do the same thing. So I was delighted to partner with them for this issue.

Below, I show you how to compare columns with an elegant formula. But this is also the bread-and-butter type of job that Power Tools does. So check them out below.

Happy Halloween for Thursday! πŸŽƒ


Brought to you by: Power Tools for Google Sheets

A set of 40+ solutions for daily tasks in Google Sheets

Still doing things manually or juggling formulas in Google Sheets? With Power Tools, you can finally let go of the hassle.

Think of it as the Swiss Army knife for your adventures in spreadsheets β€” packed with tools that let you work smarter, not harder. No formulas, scripts, or custom solutions, just instant results.

With Power Tools, your sheets will practically run themselves!


➜ News

I. Any AppSheet builders out there?

One of the more challenging aspects of building apps with lots of users is, well, managing all those users.

To solve this, my friend and fellow GDE, Vo Tu Duc, has created Onboardly, an AppSheet app to automate user onboarding and management.

​​Learn more here >>​


➜ Sheets Tip #322: Comparing two columns

In ​tip 290​ (February 2024), we looked at formulas to compare two lists of numbers. Today I want to build on that and show you a nice formula that returns the values that exist in only one of the lists.

Consider this data:

We want to create a formula that will find all the values in column 1 that are NOT in column 2.

For example, the value "FL53802" occurs in both columns, so I don't want it to show in the new list.

Let's create the formula that builds this new list.

Start with this ​XMATCH function​ in cell B2:

=XMATCH(A2:A,D2:D)

In this example, it gives an #N/A error message because it doesn't find the first value of column 1 in column 2.

Wrap it with ISNA to test if it's an error or not:

=ISNA(XMATCH(A2:A,D2:D))

This gives a TRUE value.

So far, so good. But it's only giving an output for the first row, how do we expand it to all the rows?

Ah ha!

This is where the magical FILTER function really shines. It will take the array inputs and give us array outputs, without needing an array formula designation.

Add in the ​FILTER function​ so that our formula in cell B2 looks like this:

=FILTER(A2:A,ISNA(XMATCH(A2:A,D2:D)))

And voilΓ !

Like ​last week's tip​, this is another elegant formula that uses the special array properties of the FILTER function.

➜ Tables πŸͺ‘πŸͺ‘π“Š³

This week in Sheets Insiders, we're looking at the new Tables feature in Google Sheets.

We'll be answering questions like:

  • When should you use them?
  • What are the best practices for using them?
  • How do structured Table references work?

Look out for the Sheets Insiders email in your inbox on Thursday!


If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Julius Caesar's last request...​​

Google Sheets Tips

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #338, your Monday morning espresso, in spreadsheet form! After a last bite from winter last week, it looks like spring is finally here where I live on the east coast of the US. The daffodils are growing, the box turtles are out sunning themselves on logs by the river, and temperatures are forecast in the 70s (in the 20s celcius). Wherever you are, I hope you're enjoying the changing of the seasons. ➜ News I.Google...

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #337, your Monday morning espresso, in spreadsheet form! I'm sharing a simple pivot table trick today that's one of those hidden gems in spreadsheets. Once you know it, you'll never go back. Suppose you create a complex pivot table and you want to create a second version so you can try a different filter perhaps. Well, today's trick lets you quickly duplicate the pivot table, rather than having to build again from the...

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #336, your Monday morning espresso, in spreadsheet form! This week, we're looking at use cases for the text rotation tool. I don't use it often but it's a nice technique for styling your Sheets. I've used it to add branding to my Sheets (see use case 2 below). Find tip #336 below our sponsor message and a few articles to share from the spreadsheet world. Brought to you by: BudgetSheet Save Time This Tax Season With...