List Differences in Two Cell Ranges in Google Spreadsheet

Another tip from my recent work in Google Spreadsheets.

I had two columns of data and was looking for a way to display items that appeared in one column that didn’t appear in the other. The combination of the FILTER and MATCH functions is how I accomplished this.

The FILTER function returns items from a source range that meets a specific criteria. The first argument in the FILTER function is the source range. The second argument (and further optional arguments) is the criteria. The example given by Google is as follows:

=FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)

In this example items from range A2:B26 will appear if their value in column A is greater than 5 and their value in column D is less than 10.

This was only part of my solution. I still needed to compare my two columns as the criteria of the FILTER function. I did this using the MATCH function. MATCH is very similar to FILTER. It returns the position in a range based on a criteria. For example if you have a list of the days of the week days (Sunday, Monday, Tuesday, etc.) and your criteria was "Wednesday" you end up with this:


"Wednesday" is your criteria, A1:A7 is your range, 0 is your search type (exact match when range is unsorted). This will return 4 since Wednesday would be fourth in your list.

Throw in ISERROR to check whether MATCH is returning an error and you have the following:


In this example columns A and B both include a list of the days of the week. If column B is missing a day (or more) this formula will return the missing day(s).

Pretty neat.

Comments to this post

Tags: , , ,

Access Data From Another Spreadsheet in Google Spreadsheets

I’ve been working with Google Spreadsheets a bit lately and have learned a few tricks. One such trick was learned when I had to find a way to have data in one spreadsheet automatically update based on data that’s in another spreadsheet. This turned out to be quite easy once figured out. Here’s how to do it:

1 – Every spreadsheet has a key. This key can be found by looking at the “key=” parameter of the URL for the spreadsheet. Make a note of the key of the spreadsheet you want to capture data from (your source).
2 – Make a note of the cell range the data you need resides in.
3 – Go to the spreadsheet you want to include the data in (your target) and include the following formula at the location you want the source data to appear:
=IMPORTRANGE("abcd123abcd123", "sheet1!A1:C10")

In this example replace “abcd123abcd123” with the key of your source spreadsheet and “sheet1!A1:C10” with the cell range you want.

Comments to this post

Tags: , , ,

My Palm Blog Is No More

I finally did it.

I had an old blog called Dave’s Palm Pages in which I used to write about the old Palm products (Palm Pilots and their descendants) and hacks for them. It’s also where I posted links to downloadable sports schedules and other calendar data that could be imported directly into the Palm calendar app.

Although Palm went away several years ago I still kept the blog around. I figured since it was already there and it was costing me nothing to keep it there I may as well just leave it alone.

But now the time has come. In an effort to consolidate and delete old files I have finally removed my old Palm blog. I imported it’s posts, pages and images into this blog for posterity but the blog itself is now gone.

Goodbye, Palm Pages.

Comments to this post


I Didn’t See the Baby

The video above is alarming. It shows a crime committed in the Bronx, New York earlier this week where a man took a running start to knock a woman and steal her cellphone. What makes it worse is that the woman was carrying a baby at the time.

This video look awfully heartless. But as if this was bad enough the perpetrator of this crime, after he was arrested, apologized telling reporters “I didn’t see the baby.”

Really? Seeing the baby would have made a difference? Running at and violently knocking down a defenseless person is ok if there is no baby involved? And for what? A cellphone that’s trackable and will get you caught?

The stupidity of this act and the rationalization of the perpetrator are galling. Sad that this is the world we live in.

Comments to this post

Tags: , ,

Get Jetpack Publicize and All In One SEO to Publish Posts to Facebook

I’ve been using Jetpack to cover some functionality on a few blogs and the functionalities I’ve been using (Jetpack has many functions and I only use some of it) have been functioning very well. But I ran into a problem with the Publicize functionality on one particular blog.

This blog uses Publicize to post to Twitter, Google Plus, LinkedIn and Facebook. (Publicize can also post to Tumblr and Path.) At some point over the last several months (I don’t know exactly when) Publicize stopped posting to Facebook. I scoured through the settings of the blog in question and couldn’t find anything that changed so I reached out to Jetpack support.

It turns out that the All in One SEO plugin, which does exactly as it’s name would imply, made a change a while ago in the way they implement Facebook Open Graph meta tags such that these meta tags were not being generated for this blog. With no Open Graph meta tags being sent by Publicize, Facebook rejected the post.

The fix for this is quite simple:
1 – In WP-Admin go to All In One SEO -> Feature Manager.
2 – In the Social Meta box click the “Activate” button.
3 – Now go to All In One SEO -> Social Meta and you’ll be able to configure Open Graph and Twitter tags there.

With this feature turned on in All In One SEO posts started to be properly sent to Facebook. Case closed.

Comments to this post

Tags: , , ,