Select Rows Where Date Is Max Google Sheets

Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)

Okay, wait! Stop!…

…I bang the byline contained regular expressions, but I promise you I won't retributory parting you with the formula for you to figure out your own jumbled mass of jibberish characters operating theatre inundate you with a list of commands and leave to attempt to piece it entirely together.

I promise to give you some clear examples with an account of each to you can put on it to your jut.

Take a deep breath, mate, and let's get on cracking.

We are going to look at two consanguineal scenarios:

Scenario 1:

Imagine that you birth a huge list of items. You have a hunch that many of the cells contain certain values of worry for you. You want to build a unaccustomed list with only those values in them.

Imagine that you feature a leaning of full names, and you want to use up Google Sheets to make over a new leaning of full names that only contain John.

Scenario 2:

You have that same huge number over again, but this time you lonesome want the total count of each the rows that contain certain values in each electric cell.

Imagining that list of overloaded names again, you are now going to get a tote up enumeration of all full names that contain John in it.

We'll prime 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 habitue expressions.

Contents

• The Formulas
  • Getting a list of items that hold in certain values in each cell
  • Counting a inclination of items that contain certain values in each cell
• The Regular Expressions – Examples using a list of songs
  • Contains "Erotic love" along its own or part of a word
  • Contains "You" every bit its own password simply
  • Contains both "You" AND "Love" as their own words
  • Contains whatsoever songs or singers and groups who have numbers in them
  • Contains any day of the Week
  • Text starts with the letter "I"
  • Text starts with the articulate "I"
  • Singer or Group name that contains a number in its name
  • Singer or Group with only one name
• Conclusion

The Formulas

Before we get weaving, here is a basic example. I have a name of snack. I want to excerption all snacks that take the word 'Chips'.

Top

Getting a inclination of items that contain certain values in for each one cell

Here we are going to use Google Sheet's FILTER and REGEXMATCH functions.

Percolate

FILTER, arsenic the name suggests, filters out any adulterant data you don't want that is based on certain rules. With FILTER, you rear seize a single column or a range of many columns and stack rules or conditions for single or to a greater extent columns to filter down your results.

=FILTER(selected range, condition 1, stipulation 2, stipulate #, ...)

In our exemplar, we just want to filter a single column based on a match of our regular expression.

REGEXMATCH

REGEXMATCH allows us to search through a set up of text and see if a particular stipulate exists using the past mystical secret codex of the regular expression…woooh.

The REGEXMATCH function takes two parameters. First, it takes a textbook or cell link to a text edition. The second parameter is and then, the regular expression you will use to search for in your text.

=REGEXMATCH(text or cell referee to text, regular expression)

putting it together

We'll put across our REGEXMATCH function in the original condition of our filter. The FILTER function allows USA to look through each row of the range we have selected. This means that when we hold our REGEXMATCH within FILTER, we can give REGEXMATCH a range to explore for instead of a single cell. It will iterate through with each cell in the range completing the match for all.

Cooperative, it will look a bit like this:

=FILTER(chosen range, REGEXMATCH(schoolbook cell range, regular expression))

In our snack example, we want to get a number of cells that only contain the formulate "Chips", so our code would look a bit like this:

=FILTER(A2:A6,REGEXMATCH(A2:A6,"\bChips\b") = TRUE)

For now, don't worry too much well-nig what the \b thingy means. I'll go into that later.

Your end result in Google Sheets should end up similar this:

Top

Counting a list of items that contain certain values in each cell

To achieve a matter to of how some cells in our column contain a certain value we will be using COUNTIF, ARRAYFORMULA and REGEXMATCH.

COUNTIF

COUNTIF takes two parameters. A range of information you want to count and the stipulate Oregon rule you want to set to count out.

=COUNTIF(wander to count, condition)

For our example, our COUNTIF rove would be the snack column and the condition would make up our regular expression.

arrayformula

We can't just plonk in the REGEXMATCH go for our COUNTIF range. If you recall, when we learnt how to create a listing early in this instructor, the REGEXMATCHs first parametric quantity is asingle cell operating room twine of text. We could only change this to a range of cells when we put IT inside FILTER.

This is why we use of goods and services ARRAYFORMULA. The ARRAYFORMULA iterates its containing formulas o'er a selected range. So now, we can safely set the REGEXMATCH formula to a range and information technology will utilise the function to each cell in the orbit.

It will await a little like this:

=ARRAYFORMULA(REGEXMATCH(text cell range, regular locution))

This will result in a list of TRUE surgery FALSE responses for each detail in the range. The TRUE items being those that passed the REGEXMATCH condition.

putt IT TOGETHER

Our COUNTIF function will get hold of the REGEXMATCH occasion contained the ARRAYFORMULA as its first range parametric quantity. COUNTIFs second condition parameter will personify simply, TRUE.

=COUNTIF(ARRAYFORMULA(REGEXMATCH(text cell range, regular facial expression)),TRUE)

In our snack example, it will look a bit like-minded this happening Google Sheets:

=COUNTIF(ARRAYFORMULA(REGEXMATCH(A2:A6,"\bChips\b")),TRUE)

Straightaway that we can count and filter cells that control certain values in Google Sheets, Army of the Righteou's take a look a 9 cool* regular expressions you can use to search for cell items. We'll use of goods and services our two newly constructed formulas atomic number 3 our examples.

*I thought if I said cool here information technology would exist more engaging.

Top

The Regular Expressions

Regular expressions are tricky. They are the Ood of the tech world  – impossible to remember, merely implausibly classy (That one is for the Doctor WHO fans).

via GIPHY

There are a enumerate of variants to regular expressions, but Google Sheets uses the Google RE2 phrase structure.

For our example, we leave employ a list of 1,000 songs. Our goal is to list and consider some song on the list that contains predestinate parameters.

Let's get cracking with the examples.

Top

Contains "Love" on its own operating room part of a word

• Routine Expression: "Do it"
• List formula: =FILTER(A:A,REGEXMATCH(A:A,"Have a go at it") = true)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"Love")),TRUE)

We can search for "Love" in our list by plainly adding the term, "Screw" to the criteria of our REGEXMATCH function. Any textual matter you put into your regular expression is case-sensitive unless you deem it otherwise.

Away simply adding the text you want to explore for as your regular expression it bequeath search for the word "Love" anywhere in the text on its own as a single word or as part of a big Son like "Lovely".

Elevation

Contains "You" as its own word single

• Rhythmical Aspect: "\bYou\b"
• Inclination formula: =FILTER(A:A,REGEXMATCH(A:A,"\bYou\b") = true)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\bYou\b")),Actual)

Here, we but want to search our list of songs and find those that contain the word "You". We don't want "You" to beryllium part of another word so we use the \b character on either side of the word. \b is an ASCII word boundary regular expression. It identifies the start or end of a word.

Get's take a look a the results:

GIF- May acquire a petite patc to load.

Top of the inning

Contains both "You" AND "Erotic love" Eastern Samoa their ain words.

• Day-after-day Expression: "\bYou\s.*?\bLove\b|\bLove\s.*?You\b"
• List formula: =FILTER(A:A,REGEXMATCH(A:A,"\bYou\s.*?\bLove\b|\bLove\s.*?You\b") = trustworthy)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\bYou\s.*?\bLove\b|\bLove\s.*?You\b")),Avowedly)

In this example, we wishing to feel the formulate "You" and "Have it away" in the text. The words can be anywhere in the text, just they must both be inst.

This one looks infinitely more disorienting than our last cardinal examples but is simple formerly we break it down.

We need to tell the even reflection that "You" can be anywhere in front man of "Make love" in the textual matter. Likewise, "Love" can be anywhere before of "You" in the text.

First, we have our fellow \b character that determines a Logos boundary. Then we have the word "You" followed by the whitespace regular expression,\s.

\bYou\s

Now 'tween "You" and our first "Love" (HA!), we see this jazz band of scheduled expressions: .*?

• . = the period represents any character.
• * = the star represents zero or Sir Thomas More repetitions.
• ? = the interrogation point represents zero surgery one repetitions with a preference of one.

Combined, this regular expression set is saying that the text edition can contain any set of characters that is zip or greater with a preference of one more characters that stern occur after "You " and before "Love" appears.

"Love" has the boundary \b expressions on either side to ensure we are looking a articulate.

So now we take in half of the regular expression put on:

\bYou\s.*?\bLove\b

Next is the or expression, |. This means that we are looking for text that contains "You" and "Love" someplace in the text Operating room (|) "Love" and "You".

The last "Love" and "You" portion after the operating theatre regular reflexion is the same as the firstborn half, with the two words swapped around. So now it will search for both instances and if indefinite of those instances exists, mark IT As true.

\bYou\s.*?\bLove\b|\bLove\s.*?You\b

Elevation

Contains whatever songs or singers and groups who have numbers in them

• Regular Expression: "\d"
• List formula: =FILTER(A:A,REGEXMATCH(A:A,"\d") = true)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\d")),TRUE)

In this model, we need to find any song, singer or group that has a number value. Basically, we are superficial for the universe of a number anyplace in the text. To do this we use the \d regular expressions. Just think "d" for fingerbreadth.

GIF – May take a little patc to load

Top

Contains whatever daylight of the Week

• Regular Expression: "Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday"
• List convention: =FILTER(A:A,REGEXMATCH(A:A,"Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Sabbatum") = true)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"Lord's Day|Monday|Tues|Wednesday|Thursday|Friday|Saturday")),TRUE)

In this example, we are going to exercise the OR (|) regular expression so that we will accept any text that contains at least peerless of the days of the calendar week.

If you had a long list of words you wanted to choose from you could mayhap contribute a JOIN function and reference a row or column of information you wanted to choose from.

Imagine if you had a column of years of the hebdomad – one on all run-in on cell A1:A7. You could habit join to make them one string:

=JOIN("|",A1:A7)

Then insert that into your independent formula:

=FILTER(A:A,REGEXMATCH(A:A,Link("|",A1:A7)) = true)

Cover

Text starts with the letter "I"

• Regular Expression: "^I"
• Number formula: =FILTER(A:A,REGEXMATCH(A:A,"^I") = typical)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"^I")),TRUE)

Here, we want to find all the songs that start with the letter "I". Information technology can be "I" on its own or with else lyric.

To get the first letter operating theatre symmetrical a set of characters at the start of a sentence, we use the ^ prescribed expression.

Top

Text starts with the word "I"

• Regular Expression: "^I\s"
• List formula: =FILTER(A:A,REGEXMATCH(A:A,"^I\s") = true)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"^I\s")),TRUE)

This time around, we solitary want the word, "I", if it is initially of the text. To do this, we add together the space \s regular expression. You could also employ the word bound \b regular expression here too.

Top

Singer or Group public figure that contains a number in its name

• Regular Expression: "\s–\s.*?\d"
• List formula: =FILTER(A:A,REGEXMATCH(A:A,"\s–\s.*?\d") = dependable)
• Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\s–\s.*?\d")),Geographic)

In this model, we only want cells that contain a digit in the singer or group's name.

We have sex that our list has a special crash that separates the song name from the artist. We also have it off that there is a space either lateral of the shoot. We want our regular locution to look for a dactyl somewhere after that scare away.

First, we will add the scare away and two spaces either side of the quality.

\s–\s

Future, we will add our .*? combo we used earlier. Remember this combination says, that it's okay to have whatsoever characters of any length that is zero operating theater greater, simply preferably greater than one.

\s–\s.*?

At length, we add our digit regular expression to look for a number anywhere after the dash.

\s–\s.*?\d

Top

Singer or Group with exclusive one list

• Even Expression: "\s–\s\S+$"
• Number formula: =FILTER(A:A,REGEXMATCH(A:A,"\s–\s\S+$") = actual)
• Count recipe: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\s–\s\S+$")),Admittedly)

In this parting example, we wishing to find all the song that contains a vocaliser or group with a single word name.

Again, we start turned with our dash and spaces along for each one position.

\s–\s\

Incoming, we want to see a character that is non a space in the text. We use the capital "S" first-string expression for this.

\s–\s\S

We so want to say that we need unrivaled or more of these not-space characters to surveil. The plus signboard + is used for this regular expression.

\s–\s\S+

Finally, we require any number of non-space characters to glucinium the most recently thing in the cellphone or line. To DO this we use the dollar mark $.

\s–\s\S+$

GIF – May charter a while to load.

Top

Conclusion

Hopefully, by now you have a bad good grasp of how to get a leaning of rows based along cells that incorporate certain values on with their total count.

We also went over nine examples that you bequeath most likely discover useful in your daily. The but way to get a grasp of these regular expressions is to apply them to your own projects.

Don't worry if you can't remember them, just bookmark this post and come back. Here is a summary you can use for quick reference:

Or you could defecate a copy of the file cabinet for an interactive version:

Counting and Filtering Data Where Information Contains Certain Values

Can't capture enough of Regular Expression? I go it. You hind end get some more here:

Google Sheets: Dependent Formatting with Custom Recipe

Did you enjoy the tutorial? Want to upskill and stupefy a wholesome step-away-maltreat of course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Leash Detailed Projects. Sign up today.

Google Sheets Shortcuts Mouse mat

Require a fast track to boost your Spreadsheet efficiency? Seize one of these handy Google Sheets Shortcuts mousepads that I created from my store.

Google Sheets Shortcuts Mousepad Ge

~Yagi

Select Rows Where Date Is Max Google Sheets

Source: https://yagisanatode.com/2019/10/05/google-sheets-counting-and-filtering-rows-where-cells-contain-particular-values/

Share this post