Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)
Okay, wait! Stop!…
…I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt to piece it all together.
I promise to give you some clear examples with an explanation of each to you can apply it to your project.
Take a deep breath, mate, and let’s get cracking.
We are going to look at two related scenarios:
Imagine that you have a huge list of items. You have a hunch that some of the cells contain certain values of interest for you. You want to build a new list with only those values in them.
Imagine that you have a list of full names, and you want to use Google Sheets to create a new list of full names that only contain John.
You have that same huge list again, but this time you only want the total count of all the rows that contain certain values in each cell.
Imagining that list of full names again, you are now going to get a total count of all full names that contain John in it.
We’ll first go through how to create these formulas and then provide a number of clear examples on some common criteria for searching cell for values using REGEXMATCH and regular expressions.
G Suite – Paid editions, Gmail, Google Sheets, Google Docs, Google Slides, Google Drive
Recently, I had updated all the course materials in a learning management system (Not Google Classroom. Sorry Google) to only use G Suite files like Docs, Sheets and Slides for students to access.
For me, this was a pretty logical step. It allowed course creators to update their files live when they had to correct errors or make minor changes quarter-to-quarter or year-to-year. Administratively, it meant that files did not have to be accounted for, deleted and updated every time a change was made.
While time-consuming, the changeover went well as we changed all of the student resources into Google files. We set all the files to be accessible to anyone with the link can view (The organization does not have student accounts on the same domain as the staff).
However, in the back of my mind, there was a problem I knew I needed to address. The dreaded request for access to edit. With over 3,500 students on the program and hundreds of files for them to access, it would be a huge pain if some of these students clicked that view and requested edit access.
There is no way to prevent users from not being able to do this within the document’s share options. Likewise, my organisation may want to allow requests inside the domain (for example email@example.com is okay, but firstname.lastname@example.org is not).
In the first part of our tutorial, you made a copy of the raw Exchange Volume data. Then in a new Google Sheet tab called Tutorial, you made a column of UNIQUE Date Time Stamps.
Realising that this was not user-friendly you grabbed just the date data from Column Aand put it into Column Busing the LEFT function. You then used some conditional formatting to highlight every Sunday automatically.
Next, you added two columns to display the daily exchange rates for the two gateways using FILTER. You then used two colours to identify which gateway had the highest or lowest exchange rate for that day.
You then grabbed the average of the two gateways before displaying their percentage difference.
In this tutorial, we are going to make it even easier for our reader, by setting up a column to display the gateway name of the highest exchange for each day. We’ll then put in a block of columns that will display data about the day change of the average of the two gateways. Finally, we will add a SPARKLINE graph into each row so the user can, at a glance, see how the average exchange has been tracking for the past 7 days.
By the time you are done your chart should look like this:
In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:
MIN and MAX
We are then going to make our Google Sheet more presentable by doing some neat tricks with Conditional Formatting.
However, probably the most important lesson is to consider throughout the whole process is the workflow of this project. I will try and get you into my headspace and talk about:
How I came to do things in a certain way.
What I tested before applying to my Google Sheet.
The order I did things to create the test.
When I am learning something, I find the most benefit in learning by following a project. As you follow along you don’t have to read everything. Actually, I encourage you to get the basic idea of the stage before giving it a try. Then come back to the tutorial for troubleshooting.