I saw the video that you suggested, your solution is the best, fastest, simplest and very easy, really thank you. although I must add:"ExcelIsFun" was awesome too.
ExcelIsFun Hello Sir, I do watch your excel videos daily... But some time I feel irritating because I don't understand them... But your videos are excellent 👍👍👍
This is Excel brilliance. When you see the complexity of what you think is simple, you will see the simplicity of what you think is complex. Incredibly useful and flexible insight for exploiting a flatfile. Thank you!
I like that sentence! I'm glad you like the video. It's fun trying to solve cases like these. I'm glad there are others like yourself who also see the beauty in solving these challenges. Thank you!
Same, except one less index(). =IFERROR(INDEX($B$8:$B$22, MATCH(0,COUNTIF($C$7:C7, $B$8:$B$22), 0)),""). Still works, albeit infinitely slow with large data sets. One of the best videos I've seen.
Mind Blown! You are brilliant!! Thanks for this. I have looked for more than a decade for a way to do this. This is so elegant & much less klug-y than other solutions I have found. i have gotten partway there, before. but I rarely need to work with arrays. It gets fuzzy to try to recall this stuff. Thanks, again!!
Thanks Leila for all your wonderfull and informative videos, I learned a lot and with that I found an even more easy approach: Format your source as a table Put the following formula on a separate tab or hidden in cell A1 =SORT(UNIQUE(FILTER(Table2[Cities];Table2[Cities]""))) this formula returns a sorted list of unique cities without blanks from a table where it looks at the column Cities. Reference in the data validation list to cel A1#
These array-based functions are/were only available in Ghseets (+their workspace[corporate] equivalent) and I think added in Excel365 as well. The standard MS Office-based Excel is still in the woods - those functionsa re unavailable there. Similar case with Xlookup, countunique and a few other arrat formulae which have been available in Gsheet corporate for at least 2 years.
Best video on UA-cam. I have wanted to do this for years on various projects and could never get anything working, so also worked around it. Just followed your example on one of my complex solutions and works like a charm even though I'm using Mac Office365. Many many thanks. Beautifully clear example and explanation.
I’m thankful your channel exists but furious that it’s this complicated to do anything in excel. This is an example of using excel for something that a coded application attached to a database could do WAY better lol.
Best explanation of how to use OFFSET for data validation I've seen yet, well done. I've combined this with UNIQUE and FILTER for a cascading drop down based on powerquery tables. I always forget exactly how to do it and have to look it up and this is the best one I've found so far. Named Ranges help alot too. Good stuff.
Your 15-minutes video i watched about 1.5 hour to understand what am i doing when i repeat your actions on the screen. It's amazing. By the way, i already purchased two of your courses on Udemy, your solutions and approach to explain things in my Top3 Excel-Tutors Chart.
Every time I watch your lessons, I end up being in awe with the level of your Excel mastery and the innovative solutions you bring on the table!! Kudos to your wisdom!
Hello Leila, I'm impressed with your knowledge of Excel and explanation of things in a calm, soothing tone. I wish growing up, I had teachers that taught in this style!
Thanks again Leila, I was copying the formula while you were showing it. Now onto the next video to create a second dynamic list that is conditional upon the first list (depts. and sub-depts.) You made it really easy to follow - except the formula - never could have come up with that myself. So handy when you're under pressure to get things done!
You're welcome Dianne - glad this helps. Don't forget, you can also download the workbooks from the link provided (you get to the blog post and then scroll right down)
This video is so awesome and yet very tricky. One has just keep practicing this. It's so much nesting that my head is still spinning. Gotta say that your way of eliminating the spaces in the list (after using iferror) was more straightforward than mine. I said counta range-countif(range,"")
If the youtube providing any unlimited like option then I will spend my life full of pressing like button for this video... Thanks a lot more may God bless you dear
I like your channel Leila. It is very easy to follow at the same time contents are very rich. Also you explain couple of different options to come to same answer. I really like it. Please keep posting new videos.
One tip: Since wildcard characters you used only work with text, I suggest instead using "COUNTA - COUNTBLANK" that works with both text and numbers. Love your work!
Hi Leila, I could create a Unique drop-down list, by using UNIQUE, FILTER and then created a combo box to get a Unique drop down. All thanks to your wonderful tutorials and the way you explain each and every function in detail.
Interesting, I will have to give this a try! I had always used the following array formula to extract unique items from a range, where "data" is the same range reused over and over: {=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data) ) ) , MATCH(Data, Data, 0), "") , ROW(INDIRECT ( "1: "&ROWS (Data))))) }
kindly tell me video for adding or deleting say 150-cells which exist in-between of a big excel file. how to delete or adding cells inside (say at mid of the file) ? regards
Laila. I watch your videos with full attention but these sudden adds breaks it up. Thanks for sharing so nice videos. I hope you also give lectures on data science too.
I have no words for this. How did you ever figure that combination of functions, including the reversed countif, out. I take my hat of. And the best part is that this is excactly what I was looking for. From now on, first research stop: Leila!
I'm glad this is exactly what you were looking for! Thank you for your feedback and support. The knowledge I share and the solutions I find are a combination of watching, reading and learning from the other brilliant Excel online team members and also playing around and testing things out. There are so many interesting solutions out there - finding them and learning in the process is the fun part :)
Very useful. I would have thought Excel would have something like this already built in, since it's likely to be needed quite often. But obviously they don't. So I'm glad to see you take this on. Thanks Leila. I'm definitely going to make use of this one.
Leila, This is probably the most complicated (one may call it wild) integrated function I have ever come across. But the way you explain; made it very easy. Mind blowing
True! It is quite complex. But I just did a video for the dependent drop down version of this which is probably slightly more complex -- I need to edit the video, should be out in a few weeks....
Impressive and super-smart way of tackling the problem! Love it. And you really are the perfect Index/Match teacher - your knowledge and application of these functions is really top-notch. Thank you for sharing... ...But I still wish for and I’m still counting the days until we get the new calc engine at work so that we can use dynamic arrays and the new functions to make this really easy to solve.
Another great tutorial with explanations I’ve never seen anywhere else. I have data validations with excess blanks everywhere. Those are going to be so much neater now. 👍🏻🤓
Happy New Year Leila I have just started a project and this was the first hurdle I was trying to overcome. Can't wait to try this. Excellent video as ever
This is the first time I had no clue what Leila was doing. I'm not familiar with Index and match. I learnt about the iferror. And my focus is now to learn index and match
Great job, I already tried this but it runs very slow when I tried to get the unique names on a thousands list of names. But, still a very good and useful tutorial. Keep it up.
Grab the file I used in the video from here 👉 pages.xelplus.com/unique-list-file
Thanks Leila !!
Expounded perfectly. Thank you.
But doesn't work in Excel 2007 unless I go to each cell individually and enter F2, then F9.
Thank you MA'am
I will do it through your suggestion
I saw the video that you suggested, your solution is the best, fastest, simplest and very easy, really thank you. although I must add:"ExcelIsFun" was awesome too.
Unique List & Sort Formulas - hardest ones around!!! Great work on the video !!!
ExcelIsFun
Hello Sir,
I do watch your excel videos daily...
But some time I feel irritating because I don't understand them...
But your videos are excellent 👍👍👍
Thanks a lot Mike :) yes, and also fun to tackle.
yes.
This is Excel brilliance. When you see the complexity of what you think is simple, you will see the simplicity of what you think is complex. Incredibly useful and flexible insight for exploiting a flatfile. Thank you!
I like that sentence! I'm glad you like the video. It's fun trying to solve cases like these. I'm glad there are others like yourself who also see the beauty in solving these challenges. Thank you!
Same, except one less index(). =IFERROR(INDEX($B$8:$B$22, MATCH(0,COUNTIF($C$7:C7, $B$8:$B$22), 0)),""). Still works, albeit infinitely slow with large data sets. One of the best videos I've seen.
Thanks for the great formula :)
Leila Gharani... You are the best in excel. I watch most of your videos and it really helped me a lot in my professional life.
Thank you for the nice comment! I'm really happy to hear you find the videos useful for your work :)
Mind Blown! You are brilliant!! Thanks for this. I have looked for more than a decade for a way to do this. This is so elegant & much less klug-y than other solutions I have found. i have gotten partway there, before. but I rarely need to work with arrays. It gets fuzzy to try to recall this stuff. Thanks, again!!
Thanks Leila for all your wonderfull and informative videos, I learned a lot and with that I found an even more easy approach:
Format your source as a table
Put the following formula on a separate tab or hidden in cell A1 =SORT(UNIQUE(FILTER(Table2[Cities];Table2[Cities]""))) this formula returns a sorted list of unique cities without blanks from a table where it looks at the column Cities.
Reference in the data validation list to cel A1#
These array-based functions are/were only available in Ghseets (+their workspace[corporate] equivalent) and I think added in Excel365 as well. The standard MS Office-based Excel is still in the woods - those functionsa re unavailable there. Similar case with Xlookup, countunique and a few other arrat formulae which have been available in Gsheet corporate for at least 2 years.
Best video on UA-cam. I have wanted to do this for years on various projects and could never get anything working, so also worked around it. Just followed your example on one of my complex solutions and works like a charm even though I'm using Mac Office365. Many many thanks. Beautifully clear example and explanation.
My pleasure Darren. Glad it worked on your Mac too :)
I’m thankful your channel exists but furious that it’s this complicated to do anything in excel. This is an example of using excel for something that a coded application attached to a database could do WAY better lol.
Best explanation of how to use OFFSET for data validation I've seen yet, well done. I've combined this with UNIQUE and FILTER for a cascading drop down based on powerquery tables. I always forget exactly how to do it and have to look it up and this is the best one I've found so far. Named Ranges help alot too. Good stuff.
Your 15-minutes video i watched about 1.5 hour to understand what am i doing when i repeat your actions on the screen. It's amazing.
By the way, i already purchased two of your courses on Udemy, your solutions and approach to explain things in my Top3 Excel-Tutors Chart.
Glad the video is helpful. I hope you enjoyed the courses.
Every time I watch your lessons, I end up being in awe with the level of your Excel mastery and the innovative solutions you bring on the table!! Kudos to your wisdom!
Thank you for the kind words. There is still so much to learn though :) that's what makes Excel fun. Thank you for your support.
True! 'Learning' is a never-ending journey.
Hello Leila, I'm impressed with your knowledge of Excel and explanation of things in a calm, soothing tone. I wish growing up, I had teachers that taught in this style!
OMG! how did I miss this channel, one of the best excel tutor....subscribed!
Animal...
Excellent One Mam to avoid CSE ... Your explanation and making the viewers think beyond ... Thanks a lot ....
Thanks again Leila, I was copying the formula while you were showing it. Now onto the next video to create a second dynamic list that is conditional upon the first list (depts. and sub-depts.) You made it really easy to follow - except the formula - never could have come up with that myself. So handy when you're under pressure to get things done!
You're welcome Dianne - glad this helps. Don't forget, you can also download the workbooks from the link provided (you get to the blog post and then scroll right down)
Thank you although I am a naive in excel but your explanation are lucid and open the thought process, please keep posting your new videos.
OMG. Nothing else just simply OMG! You have earned a new level of respect. Well done!
everything is great even your way of speaking is so sweet. writing "thank you" is less than what you deserve
This video is so awesome and yet very tricky. One has just keep practicing this. It's so much nesting that my head is still spinning. Gotta say that your way of eliminating the spaces in the list (after using iferror) was more straightforward than mine. I said counta range-countif(range,"")
If the youtube providing any unlimited like option then I will spend my life full of pressing like button for this video... Thanks a lot more may God bless you dear
Thank you so much 😀
I like your channel Leila. It is very easy to follow at the same time contents are very rich. Also you explain couple of different options to come to same answer. I really like it. Please keep posting new videos.
One tip: Since wildcard characters you used only work with text, I suggest instead using "COUNTA - COUNTBLANK" that works with both text and numbers. Love your work!
Thanks for the tip, Lawrence!
To get a unique list..we can trim the duplicates using advanced filter... but it doesn't meet requirement like automatic update..
Really nice work...
Hi Leila, I could create a Unique drop-down list, by using UNIQUE, FILTER and then created a combo box to get a Unique drop down. All thanks to your wonderful tutorials and the way you explain each and every function in detail.
thank you, that was what I was looking for. so much easier.
Interesting, I will have to give this a try! I had always used the following array formula to extract unique items from a range, where "data" is the same range reused over and over:
{=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data) ) ) , MATCH(Data, Data, 0), "") , ROW(INDIRECT ( "1: "&ROWS (Data))))) }
First press Like button then start video
This is my new rule
Because Mam your videos are always amazing.
Thank you so much 😀
@@LeilaGharani Most welcome Mam
dear Leila , hello , i got a lot of knowledge from your excel related vidoes. thanks a lot for your nice contributions.
Glad you like them!
@@LeilaGharani thank you and stay blessed.
kindly tell me video for adding or deleting say 150-cells which exist in-between of a big excel file. how to delete or adding cells inside (say at mid of the file) ? regards
Laila. I watch your videos with full attention but these sudden adds breaks it up. Thanks for sharing so nice videos. I hope you also give lectures on data science too.
the array nature of countif is the unlocker here!! awesome Leila! Thanks!
So soothing to listen to your voice and learned some useful skills at the same time !!!
I'm glad to hear that.
Thanks laila, this is good solution for everyone still using the old version of excel.
I have no words for this. How did you ever figure that combination of functions, including the reversed countif, out. I take my hat of. And the best part is that this is excactly what I was looking for. From now on, first research stop: Leila!
I'm glad this is exactly what you were looking for! Thank you for your feedback and support. The knowledge I share and the solutions I find are a combination of watching, reading and learning from the other brilliant Excel online team members and also playing around and testing things out. There are so many interesting solutions out there - finding them and learning in the process is the fun part :)
Omg, you are awesome. I been search for a formula like yours for about 3 months. This formula is exactly what I needed. Thank you!
That's GREAT!
This is the most confusing topic so far and still you made it as simple as possible...Thank you so much Leila
Before this video my excel sheet reached 30 MB ! Now it is only 2MB!! Thank you Leila.
It's OMG you really blow my mind the count if formula when you switch the rule of criteria as range wow i never thought of that thank you Ms. Leila
You're very welcome Ismail :)
Thank you. it saved my day. Helped me in completing a project.👍
WOW…This helped way more than the lookups. Big thanks.
Simply genius. Anything you don't know in Excel?
Very useful. I would have thought Excel would have something like this already built in, since it's likely to be needed quite often. But obviously they don't. So I'm glad to see you take this on. Thanks Leila. I'm definitely going to make use of this one.
Thanks for your comment. Yes - it would be great if the Excel team come up with a feature like this soon :)
Leila, you got on my knees! :D
thanks a lot for your videos for enhancing our excel skill.
Leila, This is probably the most complicated (one may call it wild) integrated function I have ever come across. But the way you explain; made it very easy. Mind blowing
True! It is quite complex. But I just did a video for the dependent drop down version of this which is probably slightly more complex -- I need to edit the video, should be out in a few weeks....
This is an ingenious way of using COUNTIF. Excellent tutorial.
Thanks Rajesh! Glad you like it.
Content is superb, shared with my colleagues, explained with ease
Thank you for your support and sharing the videos!
Impressive and super-smart way of tackling the problem! Love it.
And you really are the perfect Index/Match teacher - your knowledge and application of these functions is really top-notch.
Thank you for sharing...
...But I still wish for and I’m still counting the days until we get the new calc engine at work so that we can use dynamic arrays and the new functions to make this really easy to solve.
Thank you Geert! I can't wait either :)
This is the second time this video helps me out to solve a problem! I can't thank you enough!
I am real like your teaching, it is very clear and helpful for me. Thank you Leila!!! Btw, I come from Taiwan!!!
That's great. I'm glad you like the tutorials. Greetings to Taiwan!
Another great tutorial with explanations I’ve never seen anywhere else. I have data validations with excess blanks everywhere. Those are going to be so much neater now. 👍🏻🤓
Thank you Philip! Glad you like the tutorial & explanation :)
Thank you so so so so much for the video. I was looking for a solution since days. Thank you so much!!
Awesome Leila.. You are a genius..
Thanks Leila. A special thanks from Uganda
Happy New Year Leila
I have just started a project and this was the first hurdle I was trying to overcome. Can't wait to try this.
Excellent video as ever
That's great! Glad this helps. Happy new year to you too Melvin.
Mam ur concepts are out of this world ..new year starts with this magic....
Wow! Thanks Jacky for the very nice comment :)
I love how you went through your logic and thought process.
Very helpful. I frequently learn useful applications of worksheet functions from you, many thanks.
I'm happy to hear that Sam. Thanks for your comment.
Thanks Leila, very useful & methodically explained.
Many questions in one class...thank you mam..
Brilliance, you are the best!! Love it
Easily the best tutorial. Though I couldn’t understand on the first instance, I just simply followed your steps and achieved the result
Nice formula, ❤️ your explanations!
Your a lifesaver Leila! Thanks!
Happy to help, Phil!
You are a life saver, Leila! Tks!
Thank you. Your channel has every answer I am looking for to finish setting up my accounting system. Eleven thumbs up.
I'm really happy to hear that! Thanks for the thumbs up :)
El Magnifico....That was done so smooothtly..Thank you so much🤗
I used your formula. Nice video. I appreciate it.
Way to go Leila. Nice use of INDEX to avoid CSE.
Thanks. It's always fun to look for CSE free formula alternatives :)
WOOOOOOOOOWWWWWWW!!!!!!!!! GREAAAAT JOB !!!!!!!!!!!!!!!!!! YOU SAVED MY LIFE )))))))))))
Glad to help :)
It looked complicated the first time I watched it but on my second run it totally makes sense. Thank you Leila
Greate way to extract unique items, thank you very much Leila for this video.
This is the first time I had no clue what Leila was doing. I'm not familiar with Index and match. I learnt about the iferror. And my focus is now to learn index and match
Love this video... great presentation and explanation. Congrats
Glad you liked it Liliana!
Hello Mam,
I like very much the way you explain each and everything in detail.
Thank you Raj! I'm happy to hear that.
Countif ?* just changed my life. Thank you! 😁
Thanks for easy way of explaining these formulas.
I'm glad you find these easy to follow :)
You do far the best thorough videos. Great presentation, explanation and really structural. I am amazed and grateful. Thanks
Wow Tnxxx q Mam
I am Frome Bangladesh.
I am ur big Fan Mam.
Very useful take on alternative to Ctrl-Shift-Enter. Also, very well explained step-by-step.
Thank you. Glad you like it.
You know what.... you are genius and exceptional person
Thank you Muhammad for the very kind comment. I am just someone who has fun with Excel :)
Hi Leila,
At the outset thank you for the wonderful video and above all your ideas. I simply admire your explanation of the formula. Simply superb!
Thanks, I applied it in my office. Felt great thanks
U r just Genius mam...
Great video Leila, it helps me get a unique list without CSE.
Glad you like it Douglas :)
I like the channel and your best explanation madam
I'm glad to hear that. It's great to have you here.
I was having so much trouble understanding this concept but you helped me finally get it so thank you!!
Dear leila thanks for making videos
Genius,Saves CSE and the use of pivot tables....Great video.....
Great Trick Leila. I always used the match and Frequency to get a unique list with CSE. This was Great. :)
Thanks John. Match & frequency is great too.
Very thanks for your contribution Leila, good trips and tricks
Thanks Fares. Glad you like the tips and tricks.
So helpful 😊...🇺🇬
Happy New Year Leila. Thanks a lot for your valuable video.
Thank you and happy 2018 to you too :)
Thanks a lot for sharing!
You r an angel :)
Great job, I already tried this but it runs very slow when I tried to get the unique names on a thousands list of names. But, still a very good and useful tutorial. Keep it up.
Thank you very much. It was exactly what I looked for.
Glad it helped!
Hi Very simple and easy to follow for excel users. Great work. Keep up the knowledge sharing
converting to a non cse formula is a stunner for me, thanks a lot
Brilliant and so helpful! I’m a fan of all your videos! Thank you!
Thank you for your support!
Thanks .. today one skill got gained
Thanks a lot , you helped me so much .