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.

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.

You are always amazing. Thanks for your most fabulous videos in terms of google sheets on KZread.

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!

Join + Filter is exactly what I was looking for. Spot on!

I spent all morning with a problem, this has saved me, thanks

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!

@ExcelGoogleSheets

3 жыл бұрын

Great to hear!

Really always helpful. Been waiting for a join query and here it is.

Just what I needed, thank you so much!

Straight to the point and helpful. Many thanks dude!

@booshong

2 жыл бұрын

Straight to the point in 16 minutes! 🙃

Amazing!!! Thank you 🇧🇷

WOW! Great video. Thoughtful, well explained, and perfect. Wish I could buy you a shot and a beer!

You saved my life and my career! Thank you so much!!!

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."

good job, THANKS for amazing content

You always save my days! Thanks

indeed a Great job !!

Thanks! Very usefull!

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!

@brysondenver6406

2 жыл бұрын

instaBlaster

this is so dang helpful

Great video, super helpful thank you!!!!!!!!!!!!!!!!!!!

Thank u bro.. i was looking for this..

Amazing as normal from your side well done

YOU ARE A THEE BEST!! May you win the LOTTO for this 😄 Thank you!!

Thanks a lot! bro!

Thanks boss🙋‍♂️

awesome tip, thank you

So nice of you.

It was really helpful in one of my excel project, Thanks a lot

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

Google sheet is amazing, thank's for the video.

Thank You. This Join filter method is useful. Could You please tell us any method to get only unique multiple match values?

Thank you solve my problems 😊😊😊

Thanks man, it was helpful.

@ExcelGoogleSheets

Жыл бұрын

👍

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!

@ExcelGoogleSheets

2 жыл бұрын

You would need to combine those tabs in an array. I have a video on the channel that shows how to combine multiple tabs.

Thank you. How to manage it if the company id was over multiple columns and not a single column

Finally, my question is solved! Thank you!

@ExcelGoogleSheets

Жыл бұрын

Great!

I wanna like this video more than 1000 times

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

this is so useful. Thank you

@ExcelGoogleSheets

3 жыл бұрын

Glad it was helpful!

Thanks 😊. Good to know that supersql can be used this way. Wondering if there's a way to revert with the outcome sheet.

@ExcelGoogleSheets

4 жыл бұрын

Just using UNIQUE function would do the reverse.

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?

@ExcelGoogleSheets

2 жыл бұрын

You can use ARRAY_CONSTRAIN function to limit the results from FILTER function.

You're a genius, thanks!

@ExcelGoogleSheets

2 жыл бұрын

Thanks!

Hi guy, you definitely are a Genius !!!

@ExcelGoogleSheets

2 жыл бұрын

:)

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 !!

Can we get the data from pivot table as well?

You are great sir thank you

@ExcelGoogleSheets

2 жыл бұрын

Thank You!

🔥 🔥 🔥

Nice work! Is it possible to show the results of "filter" function in a dropdown menu?

@ExcelGoogleSheets

4 жыл бұрын

Not without using a range. Maybe some day.

What formula applicable in excel I have a multiple value in a row and I need a result in a rows too. Continues

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)

@WesleyPicottePhotography

2 жыл бұрын

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

Жыл бұрын

👍

The Query function seems to be an easier solution (to me) now!

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?

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.

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 ?

@ExcelGoogleSheets

3 жыл бұрын

probably UNIQUE function is what you need.

@matiassaldana7689

3 жыл бұрын

It worked. There so much I don’t know. Will be watching all of your videos. Your content is amazing ! Thanks

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

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

Filter function is not valid in my excel any suggestion ?

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

7 күн бұрын

It's a custom function, so it's not going to be super performant and will have stricter array size limitations than native functions.

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!

@ExcelGoogleSheets

3 жыл бұрын

No. Your best bet is probably to use a Pivot Table with custom text function and then VLOOKUP from it.

@shubhamrawat829

2 жыл бұрын

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.

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.

¡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!

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.

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??

@texasarchitect

3 жыл бұрын

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.

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

@ExcelGoogleSheets

3 жыл бұрын

Yes, you can do subquery. That being said, you probably don't need it. Just join multiple tables in one. Check this video kzread.info/dash/bejne/Z2WdwaZtpdWcZtY.html

@ExcelGoogleSheets

3 жыл бұрын

GROUP BY should work just fine..

Thank you so much .... Can you help me to write auto data in column when data enterd in another column..??

@ExcelGoogleSheets

4 жыл бұрын

I don't understand what you're trying to do.

@hannalawwad3407

4 жыл бұрын

@@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..

@ExcelGoogleSheets

4 жыл бұрын

kzread.info/dash/bejne/oHWXsMmmibOnXZc.html

@hannalawwad3407

4 жыл бұрын

@@ExcelGoogleSheets thank you so much...

you can do what you want!

@ExcelGoogleSheets

Жыл бұрын

👍

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~

@ExcelGoogleSheets

2 жыл бұрын

Use FILTER function kzread.info/dash/bejne/fIWHzsSKlajgYc4.html

@Dreamlight000

2 жыл бұрын

@@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.

@ExcelGoogleSheets

2 жыл бұрын

@@Dreamlight000 Use the same filter function, as condition range add those columns separated by &

@Dreamlight000

2 жыл бұрын

@@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.

@ExcelGoogleSheets

2 жыл бұрын

@@Dreamlight000 Try =FILTER(A2:A7,ArrayFormula(mmult(N(B2:C7="Mice"),SEQUENCE(COLUMNS(B2:C7),1,1,0))))

Your voice is like Aaron Paul from Breaking Bad!

Is there a way to put `Join + Filter` in an arrayFormula?

@ExcelGoogleSheets

3 жыл бұрын

No.

@geraldorigins

3 жыл бұрын

@@ExcelGoogleSheets that's sad to hear. But thanks!

Sir, googlefinance returning a table having date and volume i need only volume number what can be done

@ExcelGoogleSheets

3 жыл бұрын

Yes, use index function. Check my google finance function video for reference.

2nd

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

@ExcelGoogleSheets

4 жыл бұрын

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.

@ExcelGoogleSheets

4 жыл бұрын

actually it's CHAR(number) not CODE

@robertmaluka2763

4 жыл бұрын

@@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

@ExcelGoogleSheets

4 жыл бұрын

That's what I said, place that inside of another JOIN and use the newline character for joining.

@robertmaluka2763

4 жыл бұрын

@@ExcelGoogleSheets this is what i did> JOIN(char(10),FILTER(E:E,C:C=C282)) and it worked great thanks

how can i do this to 2022 version?

@ExcelGoogleSheets

2 жыл бұрын

What software? Excel? Google Sheets?

How can I fix the error, 'custom function parameters too large' error while using Supersql?

@ExcelGoogleSheets

2 жыл бұрын

How much data do you have?

@christinemathalikunnel2681

2 жыл бұрын

@@ExcelGoogleSheets A Lot, Some of our data has a lot of rows. Is there any fix to this?

@ExcelGoogleSheets

2 жыл бұрын

There is a limit of data you can pass to custom functions. What 's a lot? How many columns & rows?

@christinemathalikunnel2681

2 жыл бұрын

@@ExcelGoogleSheets 47584 rows and 8 columns

let know

@ExcelGoogleSheets

Жыл бұрын

?

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

@ExcelGoogleSheets

3 жыл бұрын

I have a video with a Pivot Table with value field as text. I think that should work for you. Search on the channel.

@kennethforbin5604

3 жыл бұрын

@@ExcelGoogleSheets Thank you an happy holidays