Google Sheets - Intelligent Search to Filter Data
HTML-код
- Опубликовано: 10 июл 2024
- Learn how to build an intelligent search function to filter data using a scoring system in Google Sheets.
Sign up to get updates, practice files and code snippets eepurl.com/hwyGg1
#search #filter #data
Man, this is super Dope! Especially when talking to clients and searching for information on the fly. I am going to utilize this in my google sheets CRM, and this Search function will help tremendously! Thanks, Brother!
Awesome as always. I like it when you take a second to explain all your little tricks, like you did with cut vs paste to update the reference. Thanks for posting, I check everyday :)
Glad you like it and thanks for watching!
This is ultra high quality content!
Congrats dude!
Just found your channel and i'm now hooked to it!
Thank you so much!
Welcome aboard!
You channel rocks brother! Thank you for all the effort you out into this. You're literally helping people improve their careers if they take the time to watch and learn.
I appreciate that!
@@ExcelGoogleSheets please share practice file
@@thehawk40375 all you need to do is to type what's present on the screen - and you'll need to do that to practice using this knowledge anyway
@LearnGoogleSpreadsheets That is nice.
This is so helpful and you explain things so clearly and methodically! Thank you!
Your channel has elevated my work life immensely please keep doing what you’re doing
It's great to see these kind of comments.
This tutorial still rocks. With this, I was able to make search in a way, that it searches text OR a tag that can be selected from a dropbox. And it works!!! I am so happy :)
👍
Awesome the simple way you make it, I actually did it for a work task I had for months sitting there, keep it up!!!
Wow! I am so impressed with your work! Wonderful!
feww, that took a lot of digging, I was searching how to make this exact kind of search box, I did find others that linked other sheets, and some that just use the simplest method, "search-box" however this is exactly what I was looking for thank you very much, I will keep watching your videos to learn more,
👍🏻 Excellent .. i love the clever solutions, but i greatly appreciate your style of building up to them and showing how you get there.
Thank you!
Thank you very much!
You guys are the real heroes for me. Clear magicians.
Thanks a lot guys.
If you have a patrean account, I'd definitely want to contribute.
www.patreon.com/chicagocomputerclasses
Thank You!
this is a whole class :) Thank you!
Thank you for all the techniques...
LOVE your videos this is pure art!
Thank You!
God bless you dear for helping & sharing knowledge
Your videos always inspired me 💕
Thanks so much for your inspiration. That helps me a lot in my daily job.
Keep it up!!!
Happy to hear that!
your videos are gold. Keep it up guys!
Thanks! Will do!
Now its just missing sort by closest match but that's easy enough. Awesome content as always!
SORT would make it better.
Great vídeo!!
thank you dude, you solve my problem auto subscribe for you
👍
Awesome work bro I learnt a lot from you .
I appreciate that
Thank for your work! But is it possible to edit one cell directly in search sheet and it will automatically update the data in the main sheet?
Wow this is incredible.
I would love to a follow up video showcasing an Excel LAMBDA/ LET solution
Thanks! I have LET function video on the channel.
Congratulations.
You video great thanks from turkey 👏👏👏👏
Thanks
Thanks. That has helped me a lot. My application requires much the same.
:)
Thank You for making this Nice Searchbox I love it Keep it Up. I subscribed.
😀
Hi! thanks for the video, I wonder is it possible to store the data and match score formulas in google drive or clouds and create the search bar in google sheets? I have lot of data and google sheet can't help, I try to use excel but some formula didn't work...
Thank you.
Thanks for watching!
Fantastic and useful presentation! Thank you. Question (maybe already answered): is it possible to make the filter "live" without pressing ENTER each time to filter it again?
Thanks for the video! It is super helpful. I have one question. Can I get search results for the whole spreadsheet if it has more than 1 tab. How can I set up the search results to include results from all tabs on the sheet?
This is great. I love it! I got confused as to why you added the division inside the arrayformula.. would it work outside also? Thanks!
I always use ARRAYFORMULA outside. Just my preference, not a requirement.
Great inspiration! What if I wants to be able to search in both description and item number, is that possible?
Very clever!
Thanks!
You rock dude!
Thanks!
Please put more videos about data studio and related new data handling softwares and applications 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻
Awesome!
Thanks!
Interesting!
Thanks!
❤❤❤ u!
Hi! How do you search in multiple columns example on your video you are searching in column B. How about adding additional column like searching in column B, C, and D?
Great Video
Glad you enjoyed it
hello, great video, just one question , it is possible to do the search in all sheets with the same filter ?
Yes, it is possible. I watched the other video talking just about the Filter function. Here is an example =FILTER({rang1;range2},{conditionA1;conditionB1},{conditionA2;conditionB2}). Also make sure that you have the same exact number of rows created in each sheet. Hope this helps.
Hello!
I appreciate your work on RUclips videos. It helps alot. Thank you!
I'm from Russia, Saint-Petersburg, and I had an idea about sharing your knowledge with russian-speaking people.
Would you mind if I translate your videos and upload them to our youtube channel? With links on your channel / videos of course.
Any way, thanks again, you are making the working process with google sheets a lot easier!
Hugs from Saint-P
Sorry, i'm asking you here, i sent it on email but didn't get respose so there's my another try here 💚
The same video uploaded to RUclips generally becomes an issues with RUclips copyright algorithm. Not sure this is a good idea.
THANK
Awesome.
Thank you! Cheers!
Is it possible to make it an arrayformula to match for an entire column (so that I don't have to pre-drag the formula)
It should be possible to create a custom function, otherwise unlikely.
@@ExcelGoogleSheets actually this works, in cell I2, if out of scope for this channel, maybe on get __it done? ;) =ArrayFormula(MMULT(N(REGEXMATCH(B2:B,SPLIT(TRIM('Search Here'!$B$2)," "))*1),SEQUENCE(COLUMNS(SPLIT(TRIM('Search Here'!$B$2)," ")),1,1,0))/COLUMNS(SPLIT(TRIM('Search Here'!$B$2)," ")))
Looks impressive! If you want to do a video on it I'll gladly post on the channel.
@@davidfernandezbajo Great Can't wait to try it out. But yah. probably do a video on it as OP suggested : ) Awesome!
not lower text
that was such great tutorial, you simply build a google search machine inside google sheets , kkk kkk
Bro! Do you know how to filter it and return the result from the vertical to horizontal position?
Hi very nice video.....Where are you from...I am from South India
I only have one issue whenever I input additional data in the range the search box will not populate this result., any suggestions?
How can I get this training worksheet?
how to get this dataset
Quick question: Can I create such a search filter with people's names? We deal with recurring customers.
I don't see why not.
is there some smart way to search multiple columns and in multiple sheets? when I try to make an array of filters I get an array_literal error and when I search on more than one column I end up with matches of 200%+
Yes, you can use the or operator "+" instead of the OR() function which doesn't work with FILTER() function. See an example here >>
=IFERROR(IF(B2="",ARRAYFORMULA(Settings!D5:CE1899),FILTER(Settings!D6:CE1899,(Settings!B6:B1899>=50%)+(Settings!A6:A1899>=50%))),"No matches found.")
could we apply those formula on excel without getting an errors
SPLIT function doesn't exist in Excel, so you would need to create it with VBA in order for it to work in Excel.
@@ExcelGoogleSheets thanks for your fast reply... one day i will recommended you as reference in my company
I can search engine find all excel sheet ?
how can i show all data before the filter function?
Can you share a version of this sheet?
Thanks for this incredible toturial; it works good, but it doesn't seem to work with numbers only with text, can you help me with that???
I don't really remember what exactly I did in this tutorial, but you should be able to make it work by wrapping your inputs in TEXT(inputCell,"@") function.
Is it possible to bring it to webApp ?
Sure, why not.
Hi please make a similar system but for looking some phrases in google docs, can you do it?
Google Drive search doesn't work for you?
@@ExcelGoogleSheets i means from sets of google docs files stored in google drive, eg. speech files, and then we would like to find/search keywords, eg: climate change in those sets of files....can we do that?
@@dianlaksmanaanindita9576 Yes, just use google drive search, it already has this functionality;
For some reason COUNTA is giving me a wrong answer. On a string of 6 words it returns an answer of 2. So I can't get this to work.
Check the results of SPLIT function and see what it gets you.
гений
Thanks!
WTF BRo °_____°
Your videos are fantastic. They're the first thing I look at when I start something including the spreadsheet I reference below.
Query question: is it possible to use concatenate in the data source? Something like: =QUERY(concatenate(B3,"!A1:E "),"Select * Where B is not null"). I'm using a drop down to change the data source from a bunch of other sheets in the workbook. It's working fine, but it's not an elegant solution.
Here's what's working :
=QUERY({Mobility!A1:E; Control_over_Environment!A1:E; Seating_and_Positioning!A1:E; Hearing_and_Listening!A1:E; Study_and_Organizational_Skills!A1:E; Math!A1:E; Environmental_Control_and_Computer_Access!A1:E; Oral_Communication!A1:E; Reading!A1:E; Spelling!A1:E; Written!A1:E; ADL!A1:E; Vision!A1:E; Recreation_and_Leisure!A1:E; Pre_Post_Vocational!A1:E}, "Select Col2,Col3,Col4,Col5 where Col1 ='"&B3&"' ")
Look into this ruclips.net/video/Vuh7lh2mhQk/видео.html