This is pretty good, you may want to change the use of "IF" to "Filter" as I think that will be a little more straightforward and will probably perform better over larger data sets. You could also use the "SORT" formula around the unique if you wanted things in alphabetical order.
@@MohammedRafee-yz1oo I mean to replace the if formula which returns an array in this example with the filter formula that also returns array, in both instances a formula like text join will convert the array into a delimited string. Either works just fine, I just find the use of the filter formula a little simpler/easier to read/explain than the if. If some condition is true with this range return another range otherwise return nothing, vs filter all the values I want where my condition is true. For people that don't do formulas/logical conditions as much it's just a tad harder wrapping your head embedded if statements, however in this example they are both pretty close so either way is fine.
Thanks man. Exactly what I needed. A tip for everyone that has a long list of things in a cell. Instead of just 1 or 2. And want to make a new line for each thing. Instead of [ "," ]=> replace with CHAR(10) Select the cell, choose Merge and Center on the Alighment section. Then Wrap Text. Everything should look good.
I praise my God for using you to show me how to find and join multiple results. I had been trying for several days and had been unsuccessful. Your teaching method is clear and leads to results. May God bless you and may you see His hand working in your life today. Sincerely, Becky
Really u r great... i used to watch regularly many videos on excel, but u r a person who explain things in the right way and right quantum with quality... tq bro...
Simply WOW! While we have many functions available which are good by themselves, putting them together in smart way to solve practical problems this easily, is just another thing! Great! Thanks for sharing, keep EXCELling! 😊👍
Dude you are blowing my mind. Usage and Unique and If was impressive enough...and you brought along the textjoin too. At first I thought...why aren't you using Concat formula ..but when I practiced myself..I realized that this text join is a great great enhancement to concat formula...
OMG! My mind is completely blown! This is genius! (Actually, I was first dumbfounded when you showed the UNIQUE formulas and stayed that way through the whole video. Thank you SO much!)
Thank you so much. I used this on a data set of over 100k rows and it worked perfectly. (I'm still not sure if I 100% understand and follow along, but it worked).
Excellent was searching here and there for this multiple match and landed in your explaination worked so simple ... Thanks a lot keep posting more tips
Genius bro... Actually my search for excel is stopped on your channel.... One for multiple problems... Can you please make one video on new formulas on m excel?
Sumitji, Your tutorials are always very informative, innovative, still simple to understand. We really appreciate your skills. Last time also I commented and requested you to also put up a video on how to use power query in a given scenario. Thanks in advance. Or if you have already put up such video, kindly share the reference. Thanks.
Great solution but, just an idea, if you’re using 365 then you could also use the filter function instead of if... result is the same though so your choice!
This is pretty good and you got one more subscriber on your channel. From long time I was searching how to return multiple value in one cell, thanks for sharing👍
I want to concatenate various cell reference in a single cell by "&" character. Than I want to bold some cell reference only Can this possible without VBA.
Great video, thanks. Please note that if any of the values within your referenced range contains #N/A error, the output of this formula also throws up the #N/A error.
Yes.Salute,salute and salute to my dearest ancient lost civilization God===Khilasa Temple,really Wonder of the world,craft from bed rock as hard as granite not sand stone or limestone.Masterpiece,masterpiece and masterpiece=famous 80's pop song and musics.
May I suggest you create a separate video only focusing all the new and useful formulas came in latest versions? like Textjoin? I didn't even know about this or else I would have stopped using concat formula ages ago.
@@prakashc5815 Say I have 2 columns (want to pull from cells B2:B14, and what I am looking for is in cells A2:A14), what I am comparing against (value looking for is in G4)..formula in sheets: =textjoin(",",,FILTER(B5:B14,A5:A14 = G4)). First part (deliminter), 2nd part (value if empty..I left blank), 3rd part (filter) is the condition
Great tutorial! What if I want to enter unique number and I want to see that unique number gets the data where it's assigned. how can I do that? Example if I want.to type serial number of laptop and assigned.to user, I want to see that value where it assigned. Thanks
Hi, I like ur videos and it is very useful. I have doubt. I want to maintain Inventory. Example for one reference having 100 nos. Same needs to be distributed to multiple suppliers. Once it reach the full consumption level further supplier allocation will be zero
Thanks for nice tutorial ..newbie here .. easily understand your teachings .. thanks ... i want to know .. lookup and return value in sum for example .. look for bob and return total many hours he spent in training .. pls enlighten me
Thank you so much for the video! If you have the time, I would be grateful if you could answer this question. Say that, instead of the people names, you have company names: McDonald’s Corporation, The Coca-Cola Company, Colgate-Palmolive Company, General Electric Company, Walmart Inc. And instead of the training names, you have different ways one could refer to them BUT they are in the same cell, delimited by commas and having a space before and after every term, like this: A1 B1< mcdonalds , mcdonald’s , mcdonald > A2 B2< coca cola , coke > A3 B2< colgate , palmolive , colgate palmolive > A4 B4< ge , general electric , ge healthcare , ge aviation > A5 B5< walmart > As you can see, each company name can have a varying number of keywords, but it is minimum one (e.g., walmart above). Now, say you want to build a function that looks at a column that look like this: C1 < colgate , walmart , colgate palmolive , palmolive , ge , mcdonald’s, general electric > C2 < mcdonalds , walmart > C3 < ge aviation , ge > C4 < colgate palmolive > C5 < coca cola > C6 < palmolive , ge aviation , walmart > The cells in the C column are similarily formatted to keywords on B column: they all start and end with a space and, if there is more than one keyword, they are separated by “ , “ Same as with the B column, there can be many keywords, but (same as in B) at least one one. The keywords in column C can refer to the same company or to several companies. Also, there is no keyword anywhere in C column that is not found in B column. But there are keywords in B that might not appear in C. Perhaps worth mentioning, nothing is sorted alphabetically - neither B-column keywords refering to the same company (e.g., “ palmolive , colgate palmolive ”) nor C-column keywords refering to different companies (e.g., “ palmolive , ge , mcdonald’s “) and I wouldn’t be able to easily sort either, unless told how. THE GRAND QUESTION: how do I write a formula that looks at a column like C but with 1500 rows and then searches the keywords in a column like B and returns their A match, but only one time, delimited by a “;”. Both A and B have 250 rows (i.e., 250 companies, each having at least one keyword, no blanks). I don’t care if the results are in a particular order, I just want to have each only one time. Example of desired result: function(C1) = Colgate-Palmolive Company;Walmart Inc.;General Electric Company;McDonald’s Corporation function(C2) = McDonald’s Corporation;Walmart Inc. function(C3) = General Electric Company function(C4) = Colgate-Palmolive Company function(C5) = The Coca-Cola Company function(C6) = Colgate-Palmolive Company;General Electric Company;Walmart Inc. Thank you so much for reading this and thinking about it. Would appreciate any tip! I am on borrowed time 😅
Yes.Salute,salute and salute to my dearest ancient lost civilization God===Khilasa Temple,really Wonder of the world,craft from bed rock as hard as granite not sand stone or limestone.Masterpiece,masterpiece and masterpiece=famous 80's pop song and musics.
@@neerajs.3112 Kee sai ho?T-cool.Tao mei dai car tao bo ho who's who?Tao mei lee judy cai see dei?Doom ah cha in shan who.Ji be mut sai mut dee ho,Doom ma lee ma du kar law.Moon cha tao lee tao lee Hindi art dee hei.Mei ah bee Hindi, she lai dar hung,Indian,dos bananna jar dar hung.More jar dar Hindi,Hindo see corner door bow lai wah lai Shingh see hung kar.Can/t lai lee kai ho Can/t lai nei he?Can/t lai nei he,kai ah mei tao mei key lai dar hung.Au bag dai bar kar kar hei.Bo ho ma sai he hei.Bar hei bo ho got me hei,kam beer jar dar "Ban lee" b lai.Bar hei bo ho dash bar lish hei,"chut dar"sure lure kee mut.Kam kar kar they ho?Kam mush kee hei kee lee?Ah shan hei?Ah cha.Nei he but tao.Summer lick kee,i-star bow law,Hei sai bow law,lash dar bar kar lai.Eight lai jar dar kam,ho dar hei kar jar kee saw jar dar hung,bo ho tek kee hung.Bo ho man gar hei ,easy lee single hei.Bo ho man kar hei,easy lee kar jee kee can/t lai hung kar.Guar luck kee nei he tea,mei kam what number hei.Mei jar lai ha hung,bar mei link it.Mei ban lee.So kar lee ah.Ug lai hip see dai mei link it.Salute to holly Khilasa temple,really and actual wonder of the world of lost ancient high-tech civilization not recorded exactly in history book at all.
This is pretty good, you may want to change the use of "IF" to "Filter" as I think that will be a little more straightforward and will probably perform better over larger data sets. You could also use the "SORT" formula around the unique if you wanted things in alphabetical order.
this may cause data overwrite.
"Filter" won't bring the data in one cell
@@MohammedRafee-yz1oo I mean to replace the if formula which returns an array in this example with the filter formula that also returns array, in both instances a formula like text join will convert the array into a delimited string. Either works just fine, I just find the use of the filter formula a little simpler/easier to read/explain than the if.
If some condition is true with this range return another range otherwise return nothing, vs filter all the values I want where my condition is true. For people that don't do formulas/logical conditions as much it's just a tad harder wrapping your head embedded if statements, however in this example they are both pretty close so either way is fine.
@@ahmedalhady4028 replied in another thread.
Appreciate your generosity as usual by sharing such gem of knowledge and information to the public.
I bookmarked this video and today save my day at work. Thanks... again and again
Thanks man. Exactly what I needed. A tip for everyone that has a long list of things in a cell. Instead of just 1 or 2. And want to make a new line for each thing.
Instead of [ "," ]=> replace with CHAR(10)
Select the cell, choose Merge and Center on the Alighment section. Then Wrap Text.
Everything should look good.
Thank you so Much brother❤
I praise my God for using you to show me how to find and join multiple results. I had been trying for several days and had been unsuccessful. Your teaching method is clear and leads to results. May God bless you and may you see His hand working in your life today. Sincerely, Becky
Really u r great... i used to watch regularly many videos on excel, but u r a person who explain things in the right way and right quantum with quality... tq bro...
Glad you're finding the videos useful!
I have searched for hours for this exact solution, Thank you!
Simply WOW! While we have many functions available which are good by themselves, putting them together in smart way to solve practical problems this easily, is just another thing! Great! Thanks for sharing, keep EXCELling! 😊👍
Dude you are blowing my mind.
Usage and Unique and If was impressive enough...and you brought along the textjoin too. At first I thought...why aren't you using Concat formula ..but when I practiced myself..I realized that this text join is a great great enhancement to concat formula...
Thanks for commenting Aditya... Glad you found the video useful :
Excellent! I'm happy that my logic of developing complex formulas from simple formulas are the same used by an expert like you as well...
I was trying to find a way to match multiple entries to a unique identifier and this is exactly what I needed to know. Thank you.
You just solved a big problem for me. I think you're an excellent person for making this video for the world to learn about. Thank you so much.
OMG! My mind is completely blown! This is genius! (Actually, I was first dumbfounded when you showed the UNIQUE formulas and stayed that way through the whole video. Thank you SO much!)
Thank you so much. I used this on a data set of over 100k rows and it worked perfectly. (I'm still not sure if I 100% understand and follow along, but it worked).
Thanks for this. It helped me a lot to solve my problem in writing remarks in the school form that has two conditions in one cell. God bless you.
awesome!! my issue was exactly similar and it helped me perform better in finishing up the tedious task of 2-3 hours in just one go. love you man!
Excellent was searching here and there for this multiple match and landed in your explaination worked so simple ... Thanks a lot keep posting more tips
Genius bro... Actually my search for excel is stopped on your channel.... One for multiple problems...
Can you please make one video on new formulas on m excel?
Extra Ordinary, best ever formula giving us level upto programmer level thanks....
You don't know what Importance you have in our lives thanku😊👍
Excellent... My search for a solution ended after seeing your video..Great ...keep it up
Superb bro I subscribed you immediately after this. This is how I want videos to be clear, crisp, nothing extra only to the the point discussion.
Brother you are a legend, thank you for this saved me days of struggle. Gave that man a Bells!!!!
Thank you so much I was wasting time trying to do this same exact thing and your video gave me quick and easy fix!
Straight to the point. TY. Was getting frustrated with all the other excel videos out there.
Sumitji, Your tutorials are always very informative, innovative, still simple to understand. We really appreciate your skills. Last time also I commented and requested you to also put up a video on how to use power query in a given scenario.
Thanks in advance. Or if you have already put up such video, kindly share the reference. Thanks.
a great method in simplifying and explaining how formulas go accurate
Great solution but, just an idea, if you’re using 365 then you could also use the filter function instead of if... result is the same though so your choice!
This is pretty good and you got one more subscriber on your channel. From long time I was searching how to return multiple value in one cell, thanks for sharing👍
What a man what a man .....superr mind blowing big fan of you sir
You have saves a few days of my life by providing this formula.
Appreciate your insights. The best thing is that you don't beg for "subscribe".
Plz record videos in high volume as the volume is quite low. Thanks.
Thank you for this. Had been struck in this problem for a long time. Very helpful.
Brother
Your every video has a great tutorial of excel depth knowledge
I want to concatenate various cell reference in a single cell by "&" character. Than I want to bold some cell reference only
Can this possible without VBA.
This video is a life saver. Thank you, thank you, thank you..... this is exactly what i needed.
So happy to know that the video was helpful 🙂
There are many times scratching my head to do this. This is really handy.
Awesome. Your approach of this using simple formula solved my complex problem which I was struggling to achieve with Vlookup. Thank you very much
Glad the video helped 🙂
I am impressed with this video hence i subscribed to your channel. I will go through your videos during my free time.
Wow really amazing. I will going to use it. Keep sharing excel tricks.
Great video, thanks. Please note that if any of the values within your referenced range contains #N/A error, the output of this formula also throws up the #N/A error.
Thank you. This was so easy to follow compared to other tutorials I watched previously.
I was looking for this formula. Thank you and God bless.
you just saved us soooo much time, thank you from Brazil
Your tricks and teaching styles both are amazing ❤️
THANK YOU! I've been trying to figure out how to do this on and off for MONTHS without having to use pivot tables or query.
Just kidding, didn't return all the values I wanted it to
You just helped a whole team! Thanks a lot for sharing this video! :)
Thank you so much for sharing this. This will make my work easier from now on.
Excellent, Simply Amazing! This is exactly what I am looking for. Thank you very much for this video!!!👍👍👍
Love this - saved me a bunch of time trying to get creative! Thanks again!
Hi Man, really appreciate what are you doing
You really save my time!
keep up
thanks brother - I was searching for a solution throughout the internet !!!!!
This worked perfectly! Thank you so much for sharing this approach.
Was looking for this quite a while, thank you so much.
AWWWW man! This is so sweet! WOW!
Glad you liked the video Oz... How's the Excel hash challenge coming along? I am pulling my hair trying to get a coherent solution ready
Very kind of you comment Oz, great exceler, even greater person
Yes.Salute,salute and salute to my dearest ancient lost civilization God===Khilasa Temple,really Wonder of the world,craft from bed rock as hard as granite not sand stone or limestone.Masterpiece,masterpiece and masterpiece=famous 80's pop song and musics.
This is an awesome set of formulas for cleaning up your data and presenting it in a concise way, thank you for sharing!
thanks brother, i really wanted to seek this one, in my daily working, it really helps
Nicely done. I use Excel everyday. I can see how this could be useful.
Thanks a lot this is what exactly i am looking for long days 👍👍👍👍👍👍
So easy you explain, it is quite an easy to learn
You are amazing and a life saver. Just saved me 2 hours of work. 😁
This was Truly EXTREAMOLY HELPFUL, TAHNK YOU SO MUCH MAN
Glad the video helped 🙂
Brilliant code! Nice!! Exactly what I’m looking for!!!
Very powerful and to the point, as always. Great Summit!! Thank you.
Great Tip and Tutorial, thank you so much for sharing your knowledge
Very informative.Please upload a video for same topic in Excel 2019 or lower version.
Awesome, this did exactly what I needed and was explained well. Thank you!!!
Thank you very much my friend. Doing the Lord’s work
A different way
having very Useful Technique! Thanks a lot
I doubt whether Leila is watching your videos too 😉
She uploaded a video showing how to hide cells using custom formatting a couple of days ago 😄
Best ever! Doesn't take much computational power as well!
May I suggest you create a separate video only focusing all the new and useful formulas came in latest versions? like Textjoin? I didn't even know about this or else I would have stopped using concat formula ages ago.
Thank you so much for sharing this. Very useful to me.
beautifully explained. It is an apt solution i am looking for. thanks a lot.
New thing learned today, nice bro keep your work going
NICE, like it. Also in Google Sheets replace the IF function with a FILTER function. Works perfectly. Thanks
No…how
@@prakashc5815 Say I have 2 columns (want to pull from cells B2:B14, and what I am looking for is in cells A2:A14), what I am comparing against (value looking for is in G4)..formula in sheets: =textjoin(",",,FILTER(B5:B14,A5:A14 = G4)). First part (deliminter), 2nd part (value if empty..I left blank), 3rd part (filter) is the condition
Wow, thank you! Everyone else using complicated formulas... (OK, Unique is a new formula, though, great anyway)
Been searching for this function for ages.
Thanks for making it simple. God Bless
Amazing, great formula conveyed in very simple way
I never found this formula on whole RUclips. I hàve used if, unique and textjoin formulas hundred times but never think like this formulà, Great Sir.
Besti informative videos thanks a lot
Exactly what I needed and great explanation
Thank a lot ,I want to know this function for a long time
This is absolutely amazing. You never cease to impress :D
Thanks for your valuable information regarding his topic.
Excellent. Pretty good way to use nested functions. Would be very obliged if you show how power query shall be used in this case.
Surely i am going to watch your all videos😁
Excellent!!! Thank you for the unique formula!!!
this technique is so useful! Thanks alot sir for sharing it :)
Great tutorial! What if I want to enter unique number and I want to see that unique number gets the data where it's assigned. how can I do that? Example if I want.to type serial number of laptop and assigned.to user, I want to see that value where it assigned. Thanks
This is a lifesaver, thank you very much.
Sumit you are a life saver!!!
Hi, I like ur videos and it is very useful. I have doubt. I want to maintain Inventory. Example for one reference having 100 nos.
Same needs to be distributed to multiple suppliers. Once it reach the full consumption level further supplier allocation will be zero
A good replacement to lookup functions. thanks a ton!!
Thanks for nice tutorial ..newbie here .. easily understand your teachings .. thanks ... i want to know .. lookup and return value in sum for example .. look for bob and return total many hours he spent in training .. pls enlighten me
Thank you so much for the video! If you have the time, I would be grateful if you could answer this question.
Say that, instead of the people names, you have company names: McDonald’s Corporation, The Coca-Cola Company, Colgate-Palmolive Company, General Electric Company, Walmart Inc.
And instead of the training names, you have different ways one could refer to them BUT they are in the same cell, delimited by commas and having a space before and after every term, like this:
A1 B1< mcdonalds , mcdonald’s , mcdonald >
A2 B2< coca cola , coke >
A3 B2< colgate , palmolive , colgate palmolive >
A4 B4< ge , general electric , ge healthcare , ge aviation >
A5 B5< walmart >
As you can see, each company name can have a varying number of keywords, but it is minimum one (e.g., walmart above).
Now, say you want to build a function that looks at a column that look like this:
C1 < colgate , walmart , colgate palmolive , palmolive , ge , mcdonald’s, general electric > C2 < mcdonalds , walmart > C3 < ge aviation , ge > C4 < colgate palmolive > C5 < coca cola > C6 < palmolive , ge aviation , walmart >
The cells in the C column are similarily formatted to keywords on B column: they all start and end with a space and, if there is more than one keyword, they are separated by “ , “ Same as with the B column, there can be many keywords, but (same as in B) at least one one. The keywords in column C can refer to the same company or to several companies. Also, there is no keyword anywhere in C column that is not found in B column. But there are keywords in B that might not appear in C.
Perhaps worth mentioning, nothing is sorted alphabetically - neither B-column keywords refering to the same company (e.g., “ palmolive , colgate palmolive ”) nor C-column keywords refering to different companies (e.g., “ palmolive , ge , mcdonald’s “) and I wouldn’t be able to easily sort either, unless told how.
THE GRAND QUESTION: how do I write a formula that looks at a column like C but with 1500 rows and then searches the keywords in a column like B and returns their A match, but only one time, delimited by a “;”. Both A and B have 250 rows (i.e., 250 companies, each having at least one keyword, no blanks). I don’t care if the results are in a particular order, I just want to have each only one time.
Example of desired result:
function(C1) = Colgate-Palmolive Company;Walmart Inc.;General Electric Company;McDonald’s Corporation
function(C2) = McDonald’s Corporation;Walmart Inc.
function(C3) = General Electric Company
function(C4) = Colgate-Palmolive Company
function(C5) = The Coca-Cola Company
function(C6) = Colgate-Palmolive Company;General Electric Company;Walmart Inc.
Thank you so much for reading this and thinking about it. Would appreciate any tip!
I am on borrowed time 😅
Fantastic explanation - very clear
I was looking for this formula...thank you very much !
Yes.Salute,salute and salute to my dearest ancient lost civilization God===Khilasa Temple,really Wonder of the world,craft from bed rock as hard as granite not sand stone or limestone.Masterpiece,masterpiece and masterpiece=famous 80's pop song and musics.
@@keithau8159 ??
@@neerajs.3112 Kee sai ho?T-cool.Tao mei dai car tao bo ho who's who?Tao mei lee judy cai see dei?Doom ah cha in shan who.Ji be mut sai mut dee ho,Doom ma lee ma du kar law.Moon cha tao lee tao lee Hindi art dee hei.Mei ah bee Hindi, she lai dar hung,Indian,dos bananna jar dar hung.More jar dar Hindi,Hindo see corner door bow lai wah lai Shingh see hung kar.Can/t lai lee kai ho Can/t lai nei he?Can/t lai nei he,kai ah mei tao mei key lai dar hung.Au bag dai bar kar kar hei.Bo ho ma sai he hei.Bar hei bo ho got me hei,kam beer jar dar "Ban lee" b lai.Bar hei bo ho dash bar lish hei,"chut dar"sure lure kee mut.Kam kar kar they ho?Kam mush kee hei kee lee?Ah shan hei?Ah cha.Nei he but tao.Summer lick kee,i-star bow law,Hei sai bow law,lash dar bar kar lai.Eight lai jar dar kam,ho dar hei kar jar kee saw jar dar hung,bo ho tek kee hung.Bo ho man gar hei ,easy lee single hei.Bo ho man kar hei,easy lee kar jee kee can/t lai hung kar.Guar luck kee nei he tea,mei kam what number hei.Mei jar lai ha hung,bar mei link it.Mei ban lee.So kar lee ah.Ug lai hip see dai mei link it.Salute to holly Khilasa temple,really and actual wonder of the world of lost ancient high-tech civilization not recorded exactly in history book at all.
@@keithau8159 which language did you use?
@@neerajs.3112 speaking hindi,hindo
Wow nice job! God bless you more
Thank you so much! This was just what I needed!
Thanks dude.
You literally saved me.
Bless you SUMIT and thank you.