Hey there stranger!

Sign up to get access.

Find Keywords in Any Column. Create quick search dropdown to find keywords

About this Tutorial

Create a quick search dropdown to find keywords in any column.

Video Transcript

 Hey, so you might have this problem of finding keyword in columns. Now this might occur because you have some id and then you have a bunch of data, but like your data's like maybe words or some kind of like groups of words maybe. You know, you even have like a tag for in this column maybe. All of these things, right?

Maybe column comma delineated, or for a variety of reasons, you might have maybe nothing here, but this one is, you know that one and you wanna know, okay, I wanna know every one of these that has orange in it. There's only a couple, but I wanna know which one has orange. Well, It's command F you know, we can do this, right?

We can search and, oh, we found a few, right? But what if we want a list of them somewhere? So let's create a find. And we're going to add a row, a column actually, and we're gonna write all. Now you can do this on the same sheet, but sometimes you wanna do this sort of in , I don't know what to call it.

You don't wanna show it for some reason. So I'm gonna just have two, two columns in this sheet. And I'm gonna in one write ID and off. And here I'm gonna get an array. I'm gonna get an array formula of this. Okay? So that matches. Actually move that up. Just delete that. There's gonna be, oh, there we go.

I'm just gonna delete all of that. Delete. Okay. So we have all the IDs. Now we need everything that's in all of them. So I'm gonna do join, I'm gonna use join. I'm gonna delineate them by some thing, right? So this may be pipe, you can add spaces if you want, but I'm not going to. But what are the values?

The values are going to be db and now we can do this. We can just take all of that and see all of that is joined in one. Let me do that. See, everything is joined. Maybe we want it actually com delineated. There you go. Everything is is delineated by a comma. And yeah, we can copy paste this down, but we really want to.

Is we want to put, make this an entire array and then I think we can do I'm just hoping and praying

we can't do it. Ah, see join has to be a single row or a single column. So Array formula doesn't work for joint. Fine. That's totally. , we are going to just copy and paste this. We can do that. Oh, come on. Let's do that. Okay. We can just copy command C, shift command down, arrow sh, command B, and then we'll have everything.

So now, now in one row column. In one column we have everything. Okay. So, and we have the id. So all of.  matches, right? So we have this, and again, you can put this absolutely in just a column here. Say, Hey, join all that stuff together. And now find in the find sort of projecting. What I'm gonna do, we're gonna find and see what this is, is search for some thing.

So I'm gonna just click there. Query text to search is going to be actually, There's a little weirder way to do this. We can insert, right? And do find this is a fun way to do it. Maybe find and here find, what are we gonna find? We're gonna search for whatever we're looking for here. Let's call it c3.

We'll come back here, text to search, and we're gonna search here for it. Starting at one. . Yeah. One. And now go back here to C three. This is what we want to find. Straw berry. Okay. What did it find? Found at one. What did it find? Whoops.

C3.

There we go. So that, oh, I know why that messed up cuz We want C3 two B here. And this might be a funky way to do this, but it's also fun. I'm gonna copy and paste this all the way down. So now we'll have something. What happens if we have the wrong thing? What if it's like gray? Go back to all. And we have errors, right?

So if error, we're gonna just do nothing. And now we want to have a filter. So we're gonna do a filter. What are we gonna filter? We're gonna filter this all range, this entire range. We're gonna filter it by the condition that C is equal to or greater than zero. You might have some, oh, you might want to do just two and be two in the c2.

There we go. What's the na now? No matches found. N a no matches. There we go. So we can we might not want everything we actually might want only, Hey, and now what happens when we type in orange boom, we have all the IDs. So you can make this a nice, can pretty this up a little.  Fines and does it work?

Strawberry has all of them, right? So depending on how many you have in your database and how many keywords you have, you might be able to do something like take this then, then take this column, join smush it all into one place.  transpo. Split it again and transpose it, and then take the uniques. And so then you can make this, I'm not gonna do it right now, but then you can make this a dropdown menu.

But I think that would be, actually, that might not be too hard. What would that be? Equals join, we want to join it with, we want to join, oh, this.

and then this is easy. Transpose. And then split. What do we wanna split? What do we wanna split it by? Comma,

change this to true if we need to. There's everything. Oh, we have some spaces there. I think that's what we need. There we go. And then we need unique,

and those are all the options we have. We have some spaces here probably due to this. I'm going to guess I take those out. Let's go back to find. There we go. So those are all of our. And now we can change this, right Click to data validation list from a range and just list that. Save. Now we have a dropdown list of everything.

Let's go back to our db. If, you know, somebody si sets up blue, green whatever. Now go back to our find and they're all there. Blue, green, whatever, and we can find out exactly where they. , click whatever. This one, let's just double check that it's correct. Oh, there it is. Whatever. Hopefully this is fun finding keyword in a column.

We found the keywords, we put them all together, all the keyword we found, just a text. It's a really quick and fun way to create a little search function of tags or keywords or whatever you're looking for that's sort of separated through a bunch of columns. Bye.

Courses

Sheet Stories / Video Notes + ADDED: Email Notifications

00:00:00

Fast FAQS

ChatGPT Clone in Google Sheets Part 2

Create an Internal Google Sheets Add-on

Why Different Cell References in AI Integration in Sheets?

Show Sheet Tabs Based on Edit

Add Title Case to Google Sheets

Getting Started Coding in Apps Script

How to Power Testimonials with Google Forms and Sheets

Seek Errors When Coding Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

ChatGPT Clone in Google Sheets Part 1

Embed a Number in a Website from a Google Sheet

Create Navigation Like A Book or Presentation

Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet

00:29:08

Hold a Giveaway Raffle in a Google Sheet

Capture Emails from Website Form to a Google Sheet (Without Zapier)

Embed a Headline in a Website from Google Sheets

Quickstart Tutorial OpenAI API in Google Sheets

Create a new Spreadsheet from just a Name in a Sheet.

00:05:21

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?

00:05:43

Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets

Create an Email Campaign Stats Calculator

00:35:13

Twitter App Clone in a Google Sheet

Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Highlight Row as You Move Your Cell Selection

Create a Timer with Apps Script

LinkTree in a Google Sheet

00:11:22

Password Protecting Data In a Google Sheet

Automatic Weekly Backup of Google Sheets

Create a CPM Custom Function (Create Better Calculators!)

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script

00:12:29

What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users

Sync Two Tabs Without ImportRange()

Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.

00:00:00

Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case

00:07:35

Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode

00:05:10

Write Your First Script

00:08:31

Find Keywords in Any Column. Create quick search dropdown to find keywords

00:09:37

Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator

00:11:20

Let's Make a Bookmarklet!

00:12:37

Troubleshooting Bitly in a Google Sheet Script

00:07:07

Unique Features - Design a Better Dashboard Part 2

00:04:13

How To Set Up Stripe Webhook to Google Sheets with Google Script

00:22:10

How to Edit a Macro

00:08:22

Sheet Stories / Video Notes + Clear 24 Hour Old Videos

00:35:10

Add A Timestamp to Task Lists (without Now Formula)

00:07:44

Make your Custom Functions Like Native Functions | Custom Function Autocomplete

00:17:58

Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop

00:23:26

New Syntax for WhatChanged Formula in Google Script

00:07:14

How to Record Macros

00:06:20

2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)

00:09:53

Deep Inside Dark Habits Google Script

00:18:54

How to Trigger Macros Daily

00:06:58

5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Apps Script

00:15:01