life saver! I was kind of scared if such nice tutorial doesn't exist, I would need to go through some very unhappy python coding hours just to preprocess my data.
@I2amOKАй бұрын
Great explanation of each element. After listening to your video I was able to understand a formula I was looking at somewhere else more clearly. Cut my learning curve significantly. I needed solution to "search for multiple values in string and return a value depending on the match."
@marwanshwaiki7772 жыл бұрын
Wow. How great is this function! It too me 3 days of thinking about a solution of this case with no luck, until I found your video. Thanks a lot Br.
@cool_balrog90347 ай бұрын
This was exactly the thing I needed. The magic is in the concatenate function. Now I can use a dropdown to call an entire list of info based on the selection of one cell. TYVM!
@tompigford3 жыл бұрын
I'd been trying to do index and match, with multiple matches, and couldn't figure out how to make it work. This is so simple but better than anyhing I could have come up with. Thanks!
@ExcelGoogleSheets3 жыл бұрын
Great to hear!
@maxoys453 жыл бұрын
I spent all morning with a problem, this has saved me, thanks
@winchellvalencia3215 Жыл бұрын
You saved my life and my career! Thank you so much!!!
@devx04 жыл бұрын
You are always amazing. Thanks for your most fabulous videos in terms of google sheets on KZbin.
@carlinli80263 жыл бұрын
Join + Filter is exactly what I was looking for. Spot on!
@farhanahbutler3876 ай бұрын
YOU ARE A THEE BEST!! May you win the LOTTO for this 😄 Thank you!!
@nickburiegi Жыл бұрын
WOW! Great video. Thoughtful, well explained, and perfect. Wish I could buy you a shot and a beer!
@Albruv3 жыл бұрын
You sir are a star! :) Initially I lacked the SUPERSQL Script but thanks to your other video that was a really quick and easy set up. Thanks so much!
@brysondenver64063 жыл бұрын
instaBlaster
@kennethforbin56044 жыл бұрын
Really always helpful. Been waiting for a join query and here it is.
@Alwaysbaggyjeans3 жыл бұрын
Straight to the point and helpful. Many thanks dude!
@booshong2 жыл бұрын
Straight to the point in 16 minutes! 🙃
@GV-gn3mj Жыл бұрын
I am a little confused here, what was done with sql could be done with query funtion as well right? because if it is the case I think query is easier
@user-ss5ot6xy7l8 күн бұрын
Thank you for this video, im using one of your video formula, specifially the second one that you mentioned, in my formula like this =IFERROR(IF(ISBLANK(A2:A),,JOIN(", ",FILTER(InventoryE!$P$2:$P,InventoryE!$A$2:$A=$B2))),"NOT FOUND") but instead of dragging this down the entrire column , is there a way to apply with arrayformula to apply for entire column and not drag it all the way down. as my sheet dynamically increases?
@javadgurmani2 жыл бұрын
It was really helpful in one of my excel project, Thanks a lot
@timur.kabizhanov Жыл бұрын
Finally, my question is solved! Thank you!
@ExcelGoogleSheets Жыл бұрын
Great!
@elricho722 жыл бұрын
Google sheet is amazing, thank's for the video.
@sandeepraobeАй бұрын
Thank you so very much . for this video. Qhick question. : is there a problem on performance using the supersql function>>>???? i have a table that grows as the user enters information, will the supersql have any performance problems in generating the output, and finally i wanted to thank you wholeheartdely for the video and solution. i wanted the last display format fullfilled, tried to reach out to support communities, Chatgpt and everywhere where i can go... you saved so much of nervous ness in finding a soluton and loosing my mind .luckily you tube suggested your channel. amazing video,
@ExcelGoogleSheetsАй бұрын
It's a custom function, so it's not going to be super performant and will have stricter array size limitations than native functions.
@martingutsch69852 жыл бұрын
Just what I needed, thank you so much!
@vanessaateixeira3 жыл бұрын
You always save my days! Thanks
@ericd.67472 жыл бұрын
Hi guy, you definitely are a Genius !!!
@ExcelGoogleSheets2 жыл бұрын
:)
@sokratakis19954 жыл бұрын
good job, THANKS for amazing content
@fcamargo844 жыл бұрын
Amazing!!! Thank you 🇧🇷
@devanampuero93579 ай бұрын
Great video, super helpful thank you!!!!!!!!!!!!!!!!!!!
@osamayunes23173 жыл бұрын
Amazing as normal from your side well done
@vijayvaravdekar13844 жыл бұрын
indeed a Great job !!
@2weak2what Жыл бұрын
It looks brilliant, thank you very much for this! But... First attempt of using on a small bunch of data - runs as gold. Second approach in the real environment where I actually wanted to use it - Error - Result too large. What are the limitations for SUPERSQL?
@ExcelGoogleSheets Жыл бұрын
Custom functions in Google Sheets have array size limits. I don't think there is any documentation stating what that limit actually is. So it's not really SUPERSQL related, it's array size limit. Not sure how large your data is.
@WesleyPicottePhotography2 жыл бұрын
Searching for days for a solution, this video saved the day. Thank you. I have a slightly different need based on the use of two pivot tables, each of which have primary and secondary row values. I need to find matches for all primary / secondary combinations. I tried this: =SUPERSQL("SELECT (all of my SELECT values) FROM ? l LEFT JOIN ? r on l.Expiration_C = r.Expiration_P and l.Strike_C = r.Strike_P", (my tables) ) Expiration_C and Expiration_P are the respective primary row values, and Strike_C and Strike_P are the respective secondary row values. This does not provide the proper output however, so I suspect the join is incorrect. I know this may be difficult to respond to here -- but if there's anything about the SQL statement that seems obviously off, please let me know. Thank you (I mistakenly posted this question to the install video initially)
@WesleyPicottePhotography2 жыл бұрын
I've determined that the script encounters an issue with handling date-based data. Expiration, mentioned in my original comment, is a date string. If I include this as the first property in the SELECT statement, then the data I'm retrieving from the right-hand table is skipped. If I include it as the second property, the output as expected. If I use Expiration as the JOIN key, the same issue occurs, where data from the right-hand table is not printed. Using Strike as the JOIN key results in the expected output.
@ExcelGoogleSheets Жыл бұрын
👍
@tracypyl46803 ай бұрын
Thank you solve my problems 😊😊😊
@onestoptechnocommercial5494 Жыл бұрын
Awesome 👍 video on Multiple matches against criteria. Now 1 question. Is it possible to automate the formula down the column instead of physically dragging down, as it works in array formula.
@ExcelGoogleSheets Жыл бұрын
Depends on which formula.
@sudheeshspillai95093 жыл бұрын
Thank u bro.. i was looking for this..
@MiaDanielle2 жыл бұрын
You're a genius, thanks!
@ExcelGoogleSheets2 жыл бұрын
Thanks!
@luisgermanorregocampano19873 жыл бұрын
Hi! Thanks for this method. There is a way that use this formula: JOIN(",";FILTER(DATA!E:E;DATA!A:A=A2:A) with an array formula? I need a autocomplete using that formula. Greetings!
@ExcelGoogleSheets3 жыл бұрын
No. Your best bet is probably to use a Pivot Table with custom text function and then VLOOKUP from it.
@shubhamrawat8292 жыл бұрын
hi do you find a way to use arrayformula or you any other approach. I would like to know because I am stuck at the same problem.
@emilianoreyes4 жыл бұрын
Thanks! Very usefull!
@takumahanaya50133 жыл бұрын
I wanna like this video more than 1000 times
@Ryanrenata013 жыл бұрын
Thankyou verymuch this is actually I try to find in 2 last month lately, since vlookup cant return multiple value and filter is to much for excell to handle to drag it down
@executiveengineertirunelve56943 жыл бұрын
Thank You. This Join filter method is useful. Could You please tell us any method to get only unique multiple match values?
@anupamk993 жыл бұрын
So nice of you.
@officialPsychTips Жыл бұрын
Thanks man, it was helpful.
@ExcelGoogleSheets Жыл бұрын
👍
@honeyrosebalili61582 жыл бұрын
Very useful, thank you so much! I would like to ask what if there are three tabs that I would like to match instead of two only which you have shown in the video. Many thanks in advance!
@ExcelGoogleSheets2 жыл бұрын
You would need to combine those tabs in an array. I have a video on the channel that shows how to combine multiple tabs.
@rakimhaqverdiyev5234 жыл бұрын
Thanks boss🙋♂️
@chaejongseik4 жыл бұрын
this is so dang helpful
@user-no1bc5ln7c10 ай бұрын
I have a column containing serial numbers with comma separated with corresponding payment amounts. I need to allocate and break up this payment amount in another table with the same serial numbers with bill value. Can you guide me
@j.bagalay1623 Жыл бұрын
Hello, i tried the 1st approach whis is the "transpose" it worked but it didn't capture some of the inputs. when i validate it, there are still discrepancies.. what could be the error on this, please. thank you so much
@myhelpdesk64142 жыл бұрын
You are great sir thank you
@ExcelGoogleSheets2 жыл бұрын
Thank You!
@abhisheknair18212 жыл бұрын
Hey thanks so much for this! Its great to learn from your videos. I have a large data set and want to return a fixed number of matches for a list of IDs. (There maybe 200 matches for a given ID, but I only want 100, for another ID I would need 80 matches). What's the best way to go about this?
@ExcelGoogleSheets2 жыл бұрын
You can use ARRAY_CONSTRAIN function to limit the results from FILTER function.
@waelkassab8053 жыл бұрын
Thank you. How to manage it if the company id was over multiple columns and not a single column
@yoyokoko51532 жыл бұрын
awesome tip, thank you
@NbaLive4ever5 ай бұрын
The Query function seems to be an easier solution (to me) now!
@matiassaldana76893 жыл бұрын
Hi! This has been very helpful in my work. Now I’ve been asked to do semthing very similar instead that i don’t have a list of employees to work with. I was given a list of delivered equipment (name and items) a name can be repeated on the list. What I don’t know what to do is to create a new list of names (with the list given) without repetition and all the items delivered. Which function should I be using? And if ?
@ExcelGoogleSheets3 жыл бұрын
probably UNIQUE function is what you need.
@matiassaldana76893 жыл бұрын
It worked. There so much I don’t know. Will be watching all of your videos. Your content is amazing ! Thanks
@charjaylorenzo54293 жыл бұрын
What formula applicable in excel I have a multiple value in a row and I need a result in a rows too. Continues
@jaggedswords3 жыл бұрын
this is so useful. Thank you
@ExcelGoogleSheets3 жыл бұрын
Glad it was helpful!
@Earningology3 жыл бұрын
Can we get the data from pivot table as well?
@disairrigation4862 Жыл бұрын
Would love it if it were possible to use arrayformula(transpose(filter so that the formula doesn't have to be copied down. I have found that filter does not work with arrayformula. Do you have an alternative solution?
@ExcelGoogleSheets Жыл бұрын
Yes. MAP, BYROW and LAMBDA functions are now available in Google Sheets which allow to do all of that. You can watch my Excel videos on these functions to see how it's done.
@nowaysir75764 жыл бұрын
hello please i am looking to match them not by = but by ( if this workd exist in one of the colums then put them togeher ) is that possible ? i do't want exact equality i just want the word to exist in the other column . please help me !!
@yazhisaiveandan3075 Жыл бұрын
Thanks a lot! bro!
@vinithjain29144 жыл бұрын
Nice work! Is it possible to show the results of "filter" function in a dropdown menu?
@ExcelGoogleSheets4 жыл бұрын
Not without using a range. Maybe some day.
@bocobox4 жыл бұрын
Thanks 😊. Good to know that supersql can be used this way. Wondering if there's a way to revert with the outcome sheet.
@ExcelGoogleSheets4 жыл бұрын
Just using UNIQUE function would do the reverse.
@saurabhdas77243 жыл бұрын
Your voice is like Aaron Paul from Breaking Bad!
@Dreamlight0002 жыл бұрын
Hello, Great and informative videos. I need help with a different type of sorting. I have a cast list of performers, each having 1 to 4 different roles. My list has the performers in a column with their roles in a cell to the left separated by commas, I would like to be able to search those cells and pull out each performer that has a match somewhere in the cell. Angel, Snow Crystal, Party Girl / Jane Doe If I'm looking for "Angel", Jane gets added to a list. How could this be accomplished? Thanks~
@ExcelGoogleSheets2 жыл бұрын
Use FILTER function kzbin.info/www/bejne/gIK2nZWHmqqtZ80
@Dreamlight0002 жыл бұрын
@@ExcelGoogleSheets ok, Thanks. Yes, got that to work. How to have the different search term in different columns? We are thinking our performers roles will be in their own columns. role 1, role 2, role 3, role 4, etc. Instead of comma separated in one cell. I found a video that works for Excel but having trouble converting to google sheets.
@ExcelGoogleSheets2 жыл бұрын
@@Dreamlight000 Use the same filter function, as condition range add those columns separated by &
@Dreamlight0002 жыл бұрын
@@ExcelGoogleSheets When I add the second condition range I only get 1 result, when I should be getting 3 results. =FILTER(A2:A7,C2:C7&D2:D7="Mice") Name Role 1 Role 2 Result= Name 6 Name 1 Clara Name 2 Mice Angel Name 3 Snow Crystal Name 4 Mice Snow Crystal Name 5 Lead Mice Name 6 Mice +++++++++++++++++++++++++++++++++++++++++++++ 1 condition range =FILTER(A2:A7,C2:C7="Mice") Name Role 1 Role 2 Result= Name 2 Name 1 Clara Name 4 Name 2 Mice Angel Name 6 Name 3 Snow Crystal Name 4 Mice Snow Crystal Name 5 Lead Mice Name 6 Mice There are actually going to be 7 Role columns in the final document. I thought the & suggestion was going to work. Any ideas? Thanks so much for the quick replies.
Thanks! It's useful to me. But I'm getting an error "Custom function parameters too large". What can I do? Can anyone please help me about this?
@nicolasvaron68823 жыл бұрын
¡Thank you so much for this video! I did everything and worked out but I am no sure why am I getting duplicated results. It brigs all the information but two times. For example I my matching column was "Email" so it brings up: - Email - Data 1 - Email - Data 2 - Email - Data 3 - Email - Data 4 - Email - Data 1 - Email - Data 2 - Email - Data 3 - Email - Data 4 An Ideas what can be causing the formula to show it two times? Thanks again for this video!
@vijaybaswant3 жыл бұрын
Filter function is not valid in my excel any suggestion ?
@manpreetsingh-me5pi Жыл бұрын
you can do what you want!
@ExcelGoogleSheets Жыл бұрын
👍
@ElDiablo1234 жыл бұрын
Hey is there a way to JOIN the outcome of a SUPERSQL query? Like having the 2nd example's outcome using the 3rd example. Basically I want to do a GROUP BY but it doesn't seem to work
@ExcelGoogleSheets4 жыл бұрын
Yes, you can do subquery. That being said, you probably don't need it. Just join multiple tables in one. Check this video kzbin.info/www/bejne/a2LMkHdqqtdpbNU
@ExcelGoogleSheets4 жыл бұрын
GROUP BY should work just fine..
@ElDiablo1234 жыл бұрын
I have a column which every cell has more than one value, separated by comma, and I want to do the exact same thing you do in the video, but for each value in each cell. How could I do that??
@texasarchitect3 жыл бұрын
This is what I'm also looking to do. Multiple cells, some have one lookup item, some have 2-3 items which need to be looked up then respond with multiple responses joined by comma.
@geraldorigins3 жыл бұрын
Is there a way to put `Join + Filter` in an arrayFormula?
@ExcelGoogleSheets3 жыл бұрын
No.
@geraldorigins3 жыл бұрын
@@ExcelGoogleSheets that's sad to hear. But thanks!
@hannalawwad34074 жыл бұрын
Thank you so much .... Can you help me to write auto data in column when data enterd in another column..??
@ExcelGoogleSheets4 жыл бұрын
I don't understand what you're trying to do.
@hannalawwad34074 жыл бұрын
@@ExcelGoogleSheets I have student attendance based RFID project so when the value of ID arrive to google sheet I want to write time stamp automatically..i follow your steps to auto timestamp and it work well but only when i enter data ..it does not work when data get to sheet from ardiuno..
@ExcelGoogleSheets4 жыл бұрын
kzbin.info/www/bejne/pHLGf5qjjrV0Y5Y
@hannalawwad34074 жыл бұрын
@@ExcelGoogleSheets thank you so much...
@Sealy_boy3 жыл бұрын
🔥 🔥 🔥
@roshanbande24993 жыл бұрын
Sir, googlefinance returning a table having date and volume i need only volume number what can be done
@ExcelGoogleSheets3 жыл бұрын
Yes, use index function. Check my google finance function video for reference.
@christinemathalikunnel26812 жыл бұрын
How can I fix the error, 'custom function parameters too large' error while using Supersql?
@ExcelGoogleSheets2 жыл бұрын
How much data do you have?
@christinemathalikunnel26812 жыл бұрын
@@ExcelGoogleSheets A Lot, Some of our data has a lot of rows. Is there any fix to this?
@ExcelGoogleSheets2 жыл бұрын
There is a limit of data you can pass to custom functions. What 's a lot? How many columns & rows?
@christinemathalikunnel26812 жыл бұрын
@@ExcelGoogleSheets 47584 rows and 8 columns
@robertmaluka27634 жыл бұрын
I was just trying figure this one out I forgot about join. is there a way to have the join and have each word be below in the same cell
@ExcelGoogleSheets4 жыл бұрын
If you use JOIN method and replace "," with CODE(10) it should work. Keep in mind I don't remember if new line was CODE(10), you may need to figure out what new line character number is.
@ExcelGoogleSheets4 жыл бұрын
actually it's CHAR(number) not CODE
@robertmaluka27634 жыл бұрын
@@ExcelGoogleSheets thanks for the help. But i need to keep the "," in. the filter is searching for a date what it will bring back we will say apples, oranges. then the next one to join is to that might be bananas, pears, grapes, grape fruit.when i join them i will have a coma. this is what I have now> TRANSPOSE(split(JOIN(",",FILTER(E:E,C:C=C282)),",")), but it brings in the items in different cells. my goal now is to put in one cell and each one with a coma will go below in the same cell. i have been doing it per cell but it takes to much space thanks
@ExcelGoogleSheets4 жыл бұрын
That's what I said, place that inside of another JOIN and use the newline character for joining.
@robertmaluka27634 жыл бұрын
@@ExcelGoogleSheets this is what i did> JOIN(char(10),FILTER(E:E,C:C=C282)) and it worked great thanks
@superjaw4 жыл бұрын
2nd
@paycentx27652 жыл бұрын
how can i do this to 2022 version?
@ExcelGoogleSheets2 жыл бұрын
What software? Excel? Google Sheets?
@jasoncarlcaasi8061 Жыл бұрын
let know
@ExcelGoogleSheets Жыл бұрын
?
@kennethforbin56043 жыл бұрын
Well, please give me this for Christmas: I have data like this Name Chemistry Math Physic Sam Yes Yes Paul Yes Yes Sam Yes How do i merge this data so that I get a unique name but merge the records for each column to get something link this. Name Chemistry Math Physic Sam Yes Yes Yes Paul Yes Yes
@ExcelGoogleSheets3 жыл бұрын
I have a video with a Pivot Table with value field as text. I think that should work for you. Search on the channel.