This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.
Hey there stranger!
Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.
Find Keywords in Any Column. Create quick search dropdown to find keywords
About this Tutorial
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.
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.
Sheet Stories / Video Notes + ADDED: Email Notifications
ChatGPT Clone in Google Sheets Part 2
Why Different Cell References in AI Integration in Sheets?
Show Sheet Tabs Based on Edit
Add Title Case to Google Sheets
How to Power Testimonials with Google Forms and Sheets
Getting Started Coding in Apps Script
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
Hold a Giveaway Raffle in a Google Sheet
Embed a Headline in a Website from Google Sheets
Capture Emails from Website Form to a Google Sheet (Without Zapier)
Quickstart Tutorial OpenAI API in Google Sheets
Create a new Spreadsheet from just a Name in a Sheet.
Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?
Email Yourself a Cell from a Google Sheet, Every Day
OpenSea Data Inside Sheets
Create an Email Campaign Stats Calculator
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
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
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.
Password Protecting Data In a Google Sheet Part 2 The Basics
Benoit Asks: How to Convert Case
Learn to Code in Google Sheets, For Programmers
Add a Checkbox to Turn on Dark Mode
Write Your First Script
Find Keywords in Any Column. Create quick search dropdown to find keywords
Basic CRM - Add a Powerful Script To Move Row Based on Status
How To Improve: 1,000 Business ideas: Business Idea Generator
Let's Make a Bookmarklet!
Troubleshooting Bitly in a Google Sheet Script
Unique Features - Design a Better Dashboard Part 2
How To Set Up Stripe Webhook to Google Sheets with Google Script
How to Edit a Macro
Sheet Stories / Video Notes + Clear 24 Hour Old Videos
Add A Timestamp to Task Lists (without Now Formula)
Make your Custom Functions Like Native Functions | Custom Function Autocomplete
Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop
New Syntax for WhatChanged Formula in Google Script
How to Record Macros
2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)
Deep Inside Dark Habits Google Script
How to Trigger Macros Daily
5 Ways to Create Coupon Codes | Create UUIDs
Create a Radio Button From Checkboxes Using Google Apps Script