Simpler formula with exact same result: "=JOIN(", ", filter( ArrayFormula(TRIM(SPLIT(A1, ","))) , ArrayFormula(ISNUMBER(MATCH( ArrayFormula(TRIM(SPLIT(A1, ","))) , ArrayFormula(TRIM(SPLIT(B1, ","))) ,0))) ) )" I took out the unnecessary "transpose" steps. Just one small tweak / cleanup... To the video creator: Awesome!! This is a completely new way for me to use sheets. I'm super stoked!!
I was always scared of Excel or Google Sheet formula. This guy deal with formula so easily, I am fascinated. Love the way you explain. Fanastic work!!👍
That was awesome. I am a seasoned excel programmer but I find that google sheets have a leg on excel when it comes to array formulas and they have some additional formulas that Excel does not have.
I appreciate the strategy of finding the result you want, and then copying the formula without the equals sign to embed the working formula string into another formula string. I will use this idea.
"=ARRAYFORMULA(JOIN(",", FILTER(TRANSPOSE(TRIM(SPLIT(A1,","))), ISNUMBER(SEARCH(TRANSPOSE(TRIM(SPLIT(A1, ","))),B1)))))" By not splitting the second column, you can use 'SEARCH' to see if the item is there. Also, I think you can pull the ArrayFormula to the start of the function. Not sure if it makes it more readable.
Great video! Many many thanks. One question: how can we deal with lists where common values are not in the same rows? I suspect that match function wouldn't work longer.... With appreciation for your work, cheers from Italy
10:34 an easier solution is to use the function SUBSTITUTE to remove ALL spaces from your string: =TRANSPOSE( SPLIT( SUBSTITUTE(range, " ", "") , "," )) If you want it in a single line format: =TRANSPOSE(SPLIT(SUBSTITUTE(range, " ", ""), ","))
Great videos - I'm still working through. Maybe you've covered this, but is there an easy way to use functions to fill in blank cells in a list so that each item copies down to blank cells below it as follows: Item 1 Blank Blank Item 2 Blank Blank Blank Item 3 Etc etc
This is helpful. But i got question for you. Is there way that i can get this kind of result. Let say i have a list of Applicants and each applicants will be assigned to a specific assessor. Now i want to get the list of the applicants assisted by that specific assessor. Is that possible using match function?
Hi, I want to highlight only overlapping Annual leaves of my employees. Only highlight if two employees are going on annual leaves on the same dates. can this be done?
Could you help me? I would like to input a time in minutes and seconds, and have it display in just seconds for printing. For example if I type in 0:10:06 or 10.6 (as in 10min 6seconds) I want 606 to be displayed in that cell. If yes, could you also do this with hours? Thanks.
Hi buddy... Very great video! But maybe you can help me... I have a google sheet to book appointments... There are 2 columns... 1st column is the date and the 2nd column is a dropdown with slots (morning, afternoon, evening).... My question is: If two different users choose the same date how can they see different options in the dropdown? For example: User A choose january 01 - morning User B choose also january 01 - but only can left the options afternoon and evening (because morning was already taken) Thanks a lot!
Is it possible to do this with columns too? Like for example: =ARRAYFORMULA(JOIN(",",FILTER(transpose(SPLIT(L:L,",")),ARRAYFORMULA(isnumber(match(transpose(SPLIT(L:L,",")),transpose(SPLIT(M:M,",")),0))))))
HI, your Video is fantastic! but what if I have a bigger tab and i want to select just 3 ranges then, if they are in more rows equals have to been copied in another sheet? for exemple i have: name, name of the teacher and level , than i have others data that do no interest me. I want to create another sheet in wich, all students with the same teacher and the same level will be put toghether, if the teacgher or the level is different d´they shoukld not appear in the next sheet. is it possible? Further I was looking also your video about combinig more cells contents in one, is it possible to do the opposite? to divide teh content of one cell into more cells? Thank you in advance, ißm looking forwards to see your next video!
Please, Please do a video using the same setup but that will result in a third list with things that are NOT in both lists. So if they are in only 1 list or the other then the formula will create one list of what is in neither original list. I can't find a video, webpage, or forum that solves this for me. If anyone has a solution let me know.
You could use the same formula, just replace ISNUMBER with ISERROR. Like "=JOIN(",", FILTER(TRANSPOSE(TRIM(SPLIT(A1,","))), ArrayFormula(ISERROR(MATCH(TRANSPOSE(TRIM(SPLIT(A1, ","))),TRANSPOSE(TRIM(SPLIT(B1,","))),0)))))"
You can use docs.google.com/spreadsheets/d/1aLJzrsC8zVZQnY90aycgWTTAN_mxw7Acsw6wBkwuVQA/copy which will help you compare 2, 3, 4 or practically any number of lists. It will show you exactly which lists every item is in, and display the results in 2 formats.
HELP ME ON THIS I want to match two column values from a list and find the matching column value. for eg : if the list contains CL1 CL2 CL3 A B 2 A C 3 A D 4 When i give A and B , it should give 2 .
"Hi I work in a small company, we bid cars to customers after paying a guarantee from an auction site in America and after winning the car the customer pays the required value and then we ship the car Topic: I have a table with customer payments and data (date / name / car / car code / receipt / exchange / balance) And the second table in the capital movement of the car that was paid to the site and data (date / name / car / car code / receipt / exchange / balance) I cannot obtain an account statement because we are considered a branch of the main account so I used the tables My question: I want a function or method to read a column in which (the car code) is in the capital movement of the car that was purchased with the column in which (the car code) is in the customer payments column and output the difference between the first and the second table Until I know who the rest is of value and who has not paid Thank you"
Simpler formula with exact same result:
"=JOIN(", ", filter( ArrayFormula(TRIM(SPLIT(A1, ","))) , ArrayFormula(ISNUMBER(MATCH( ArrayFormula(TRIM(SPLIT(A1, ","))) , ArrayFormula(TRIM(SPLIT(B1, ","))) ,0))) ) )"
I took out the unnecessary "transpose" steps. Just one small tweak / cleanup...
To the video creator: Awesome!! This is a completely new way for me to use sheets. I'm super stoked!!
I was always scared of Excel or Google Sheet formula. This guy deal with formula so easily, I am fascinated.
Love the way you explain. Fanastic work!!👍
That was awesome. I am a seasoned excel programmer but I find that google sheets have a leg on excel when it comes to array formulas and they have some additional formulas that Excel does not have.
These videos are excellent. They are by far the best. This is how a teacher should teach. Invaluable stuff. Thank you, hope you are well.
The I've learnt most from your videos is how to "build" formulas. For that I am very appreciative. Thank you.
Great to hear!
I appreciate the strategy of finding the result you want, and then copying the formula without the equals sign to embed the working formula string into another formula string. I will use this idea.
Wow, your video just saved my life. THANK YOU!
Thank u for sharing Usefull formula....
That was really well explained, thank you so much for this tutorial, I'm starting to learn from your videos
Beautiful and clean work!
Well done. I've been doing this same thing with query() but yours is more elegant and takes up less real estate!
How do you do it with query ?
"=ARRAYFORMULA(JOIN(",", FILTER(TRANSPOSE(TRIM(SPLIT(A1,","))), ISNUMBER(SEARCH(TRANSPOSE(TRIM(SPLIT(A1, ","))),B1)))))" By not splitting the second column, you can use 'SEARCH' to see if the item is there. Also, I think you can pull the ArrayFormula to the start of the function. Not sure if it makes it more readable.
Thanks! Keep doing Spreadsheets tutorials
Great video! Many many thanks. One question: how can we deal with lists where common values are not in the same rows? I suspect that match function wouldn't work longer.... With appreciation for your work, cheers from Italy
10:34 an easier solution is to use the function SUBSTITUTE to remove ALL spaces from your string:
=TRANSPOSE(
SPLIT(
SUBSTITUTE(range, " ", "")
, ","
))
If you want it in a single line format:
=TRANSPOSE(SPLIT(SUBSTITUTE(range, " ", ""), ","))
Loved it bro..🤘 ☺️
Impressive explaining
Great contribution! Thanks
Awesome explanation
Can we use name ranges from different sheet rather than list within same sheet?
Yes, you can. Just use it inside IMPORTRANGE function.
superbe!!! merci beaucoup...
Thanks
Great videos - I'm still working through. Maybe you've covered this, but is there an easy way to use functions to fill in blank cells in a list so that each item copies down to blank cells below it as follows:
Item 1
Blank
Blank
Item 2
Blank
Blank
Blank
Item 3
Etc etc
=IF(ISBLANK(A1),OFFSET(B1,-1,0),A1)
You can use array formula for that also
This is helpful. But i got question for you. Is there way that i can get this kind of result. Let say i have a list of Applicants and each applicants will be assigned to a specific assessor. Now i want to get the list of the applicants assisted by that specific assessor. Is that possible using match function?
This is awesome brother ❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️❤️
Hi, I want to highlight only overlapping Annual leaves of my employees. Only highlight if two employees are going on annual leaves on the same dates. can this be done?
Could you help me? I would like to input a time in minutes and seconds, and have it display in just seconds for printing. For example if I type in 0:10:06 or 10.6 (as in 10min 6seconds) I want 606 to be displayed in that cell. If yes, could you also do this with hours? Thanks.
Super Awesome. Is it possible to achieve HIrarchies and Levels in sheets?
Hi buddy... Very great video! But maybe you can help me...
I have a google sheet to book appointments... There are 2 columns... 1st column is the date and the 2nd column is a dropdown with slots (morning, afternoon, evening)....
My question is:
If two different users choose the same date how can they see different options in the dropdown?
For example:
User A choose january 01 - morning
User B choose also january 01 - but only can left the options afternoon and evening (because morning was already taken)
Thanks a lot!
Waou... Amazing !
Is it possible to do this with columns too? Like for example:
=ARRAYFORMULA(JOIN(",",FILTER(transpose(SPLIT(L:L,",")),ARRAYFORMULA(isnumber(match(transpose(SPLIT(L:L,",")),transpose(SPLIT(M:M,",")),0))))))
Can you explain how you do with script ? And how you do to have only the items which doesn't match in 2 lists ? Thanks for your videos !
Thanks from me too . This would be very helpful for me too if possible please?
elegant!!
What if you wanted to do the opposite thing? Show only the cells that had no duplicates? How would you do that?
Why don't you use the function "not"?
HI, your Video is fantastic! but what if I have a bigger tab and i want to select just 3 ranges then, if they are in more rows equals have to been copied in another sheet? for exemple i have: name, name of the teacher and level , than i have others data that do no interest me. I want to create another sheet in wich, all students with the same teacher and the same level will be put toghether, if the teacgher or the level is different d´they shoukld not appear in the next sheet. is it possible?
Further I was looking also your video about combinig more cells contents in one, is it possible to do the opposite? to divide teh content of one cell into more cells?
Thank you in advance, ißm looking forwards to see your next video!
I love this :)
Thanks bro
Please, Please do a video using the same setup but that will result in a third list with things that are NOT in both lists. So if they are in only 1 list or the other then the formula will create one list of what is in neither original list. I can't find a video, webpage, or forum that solves this for me. If anyone has a solution let me know.
You could use the same formula, just replace ISNUMBER with ISERROR. Like "=JOIN(",", FILTER(TRANSPOSE(TRIM(SPLIT(A1,","))), ArrayFormula(ISERROR(MATCH(TRANSPOSE(TRIM(SPLIT(A1, ","))),TRANSPOSE(TRIM(SPLIT(B1,","))),0)))))"
You can use docs.google.com/spreadsheets/d/1aLJzrsC8zVZQnY90aycgWTTAN_mxw7Acsw6wBkwuVQA/copy which will help you compare 2, 3, 4 or practically any number of lists. It will show you exactly which lists every item is in, and display the results in 2 formats.
@@DanKulibert Thank you so much! 🥳🤯🤯 Long overdue but finally got a chance to use this formula. I don't know you but your answer helped a lot! 😁
@@darbin5857 Thank you both for the answers!!
Dear sir how to do multiple column name ranged in one time in Google sheet ?
👍
Awesome !! Can we use numbers as well ?
sure.
HELP ME ON THIS
I want to match two column values from a list and find the matching column value.
for eg : if the list contains
CL1 CL2 CL3
A B 2
A C 3
A D 4
When i give A and B , it should give 2 .
ua-cam.com/video/lipWG59UJts/v-deo.html
"Hi
I work in a small company, we bid cars to customers after paying a guarantee from an auction site in America and after winning the car the customer pays the required value and then we ship the car
Topic:
I have a table with customer payments and data (date / name / car / car code / receipt / exchange / balance)
And the second table in the capital movement of the car that was paid to the site and data (date / name / car / car code / receipt / exchange / balance)
I cannot obtain an account statement because we are considered a branch of the main account so I used the tables
My question:
I want a function or method to read a column in which (the car code) is in the capital movement of the car that was purchased with the column in which (the car code) is in the customer payments column and output the difference between the first and the second table
Until I know who the rest is of value and who has not paid
Thank you"
What do you mean ?
🙏🏻
is number only take 1 argument