Grab the file I used in the video from here 👉 pages.xelplus.com/unique-list-file
@MySpreadsheetLab6 жыл бұрын
Thanks Leila !!
@rahulahuja14126 жыл бұрын
Expounded perfectly. Thank you. But doesn't work in Excel 2007 unless I go to each cell individually and enter F2, then F9.
@ModernPindawale6 жыл бұрын
Thank you MA'am
@ModernPindawale6 жыл бұрын
I will do it through your suggestion
@javadkhalilarjmandi39066 жыл бұрын
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.
@genccomo79936 жыл бұрын
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.
@LeilaGharani6 жыл бұрын
Thanks for the great formula :)
@subhadiproy40256 жыл бұрын
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.
@LeilaGharani6 жыл бұрын
Thank you for the nice comment! I'm really happy to hear you find the videos useful for your work :)
@excelisfun6 жыл бұрын
Unique List & Sort Formulas - hardest ones around!!! Great work on the video !!!
@rajkathare1236 жыл бұрын
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 👍👍👍
@LeilaGharani6 жыл бұрын
Thanks a lot Mike :) yes, and also fun to tackle.
@michalroesler3 жыл бұрын
yes.
@wampage6 жыл бұрын
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!
@LeilaGharani6 жыл бұрын
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!
@blackknight64524 жыл бұрын
everything is great even your way of speaking is so sweet. writing "thank you" is less than what you deserve
@kbjagadeesh5 жыл бұрын
OMG! how did I miss this channel, one of the best excel tutor....subscribed!
@michalroesler3 жыл бұрын
Animal...
@meghasyam4276 жыл бұрын
This is the most confusing topic so far and still you made it as simple as possible...Thank you so much Leila
@grantdrinnen20764 жыл бұрын
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))))) }
@jimcrain4124 жыл бұрын
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!!
@RonBarrett19543 жыл бұрын
OMG. Nothing else just simply OMG! You have earned a new level of respect. Well done!
@lawrenceheltzer88203 жыл бұрын
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!
@LeilaGharani3 жыл бұрын
Thanks for the tip, Lawrence!
@gurneetkaur14984 жыл бұрын
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.
@DanielFassa3 ай бұрын
thank you, that was what I was looking for. so much easier.
@ismailismaili00716 жыл бұрын
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
@LeilaGharani6 жыл бұрын
You're very welcome Ismail :)
@gf1net Жыл бұрын
Great Video! To get a sorted unique list, you can use the followings: IFERROR(INDEX(TableDiv[Division],MATCH(0,INDEX(COUNTIF(TableDiv[Division],"
@lolo266910 ай бұрын
Bravo
@almeidr6 жыл бұрын
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!
@MysteryMan1594 жыл бұрын
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.
@venkatiyer74594 жыл бұрын
Excellent One Mam to avoid CSE ... Your explanation and making the viewers think beyond ... Thanks a lot ....
@hemanthrajakuncham4 жыл бұрын
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...
@kigoindonesia31953 жыл бұрын
Thanks laila, this is good solution for everyone still using the old version of excel.
@francisocaaki4 ай бұрын
Thanks Leila. A special thanks from Uganda
@gyalbobhutia28254 жыл бұрын
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.
@DrSougataBanerjee6 жыл бұрын
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!
@LeilaGharani6 жыл бұрын
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.
@DrSougataBanerjee6 жыл бұрын
True! 'Learning' is a never-ending journey.
@kuldeep.30123 жыл бұрын
First press Like button then start video This is my new rule Because Mam your videos are always amazing.
@LeilaGharani3 жыл бұрын
Thank you so much 😀
@kuldeep.30123 жыл бұрын
@@LeilaGharani Most welcome Mam
@sideshowbobby714 жыл бұрын
I love how you went through your logic and thought process.
@johannesmp32 жыл бұрын
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,"")
@janmeijering59992 жыл бұрын
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#
@marcobanderas33062 жыл бұрын
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.
@noaman19763 жыл бұрын
Before this video my excel sheet reached 30 MB ! Now it is only 2MB!! Thank you Leila.
@rustamtau79444 жыл бұрын
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.
@LeilaGharani4 жыл бұрын
Glad the video is helpful. I hope you enjoyed the courses.
@andykoegelenberg63764 жыл бұрын
Wow So much complexity for something you can do dynamically with a pivot table in 10 key strokes and no formulas
@timbrykkk3 жыл бұрын
Leila, you got on my knees! :D
@tafheemkhattak3089 Жыл бұрын
Thank you. it saved my day. Helped me in completing a project.👍
@johannesmp3 Жыл бұрын
Simply genius. Anything you don't know in Excel?
@DP-xz8xr4 жыл бұрын
Best video on KZbin. 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.
@LeilaGharani4 жыл бұрын
My pleasure Darren. Glad it worked on your Mac too :)
@hadibq Жыл бұрын
the array nature of countif is the unlocker here!! awesome Leila! Thanks!
@shermuhammad34414 жыл бұрын
dear Leila , hello , i got a lot of knowledge from your excel related vidoes. thanks a lot for your nice contributions.
@LeilaGharani4 жыл бұрын
Glad you like them!
@shermuhammad34414 жыл бұрын
@@LeilaGharani thank you and stay blessed.
@shermuhammad34414 жыл бұрын
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
@diannedrechsler47926 жыл бұрын
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!
@LeilaGharani6 жыл бұрын
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)
@shuhaib.y75843 жыл бұрын
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
@LeilaGharani3 жыл бұрын
Thank you so much 😀
@TravisLP272 жыл бұрын
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.
@ursraj276 жыл бұрын
This is an ingenious way of using COUNTIF. Excellent tutorial.
@LeilaGharani6 жыл бұрын
Thanks Rajesh! Glad you like it.
@rodrocdl3 жыл бұрын
It looked complicated the first time I watched it but on my second run it totally makes sense. Thank you Leila
@FerrelFrequency2 жыл бұрын
WOW…This helped way more than the lookups. Big thanks.
@jackychawla54226 жыл бұрын
Mam ur concepts are out of this world ..new year starts with this magic....
@LeilaGharani6 жыл бұрын
Wow! Thanks Jacky for the very nice comment :)
@SaddamKhan-jd2sj3 жыл бұрын
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
@leofiatlux01 Жыл бұрын
You are a life saver, Leila! Tks!
@mubashir382 жыл бұрын
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.
@RameshGau4 жыл бұрын
thanks a lot for your videos for enhancing our excel skill.
@csharp1990 Жыл бұрын
This is extremely helpful! I want to create a dropdown list using this technique, but also add one additional value that is not found in the unique list. Haven't found a way to do that yet..
@MasoodAhmad-su2gp5 жыл бұрын
@Leila, I must say, your trick is great and so easy to incorporate in any dashboard. Thanks for sharing your experience and tricks with us. Believe it or not, I have used this trick in some of my dashboards and it works great. There’s only one occasion, when the formula fails to extract unique records and that is with Blank row(s). Can you please look at some other trick or formula that can extract Unique records even if if the data has blank row or rows. Anyway thanks for your videos.
@LeilaGharani5 жыл бұрын
I'm glad to hear you've successfully used this in your dashboards. Yes, you're right it will include one field with a 0 if there are many blanks in there. An easy way to overcome this, if the data has blanks is to start the data set from the 2nd row. Basically to insert a blank right after the table header. This will catch the first blank in data preparation and the dependent drop down list can be made from one cell down which ensures that subsequent blanks will not be in the middle of the unique list but always at the top and this one line can always be ignored. It's easier to handle like this than to create more complex formulas...
@sujeetsamal Жыл бұрын
Many questions in one class...thank you mam..
@sarishhendre7263 жыл бұрын
Thank you so so so so much for the video. I was looking for a solution since days. Thank you so much!!
@praveenk16724 жыл бұрын
Easily the best tutorial. Though I couldn’t understand on the first instance, I just simply followed your steps and achieved the result
@irfanmansoori30602 жыл бұрын
Awesome Leila.. You are a genius..
@philalmeida19624 жыл бұрын
Your a lifesaver Leila! Thanks!
@LeilaGharani4 жыл бұрын
Happy to help, Phil!
@JNguyenKnight6 жыл бұрын
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.
@LeilaGharani6 жыл бұрын
Thanks for your comment. Yes - it would be great if the Excel team come up with a feature like this soon :)
@muhammadusmanraja83366 жыл бұрын
You know what.... you are genius and exceptional person
@LeilaGharani6 жыл бұрын
Thank you Muhammad for the very kind comment. I am just someone who has fun with Excel :)
@shakhobiddinnakiev19843 жыл бұрын
WOOOOOOOOOWWWWWWW!!!!!!!!! GREAAAAT JOB !!!!!!!!!!!!!!!!!! YOU SAVED MY LIFE )))))))))))
@LeilaGharani3 жыл бұрын
Glad to help :)
@philipmcdonnell71686 жыл бұрын
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. 👍🏻🤓
@LeilaGharani6 жыл бұрын
Thank you Philip! Glad you like the tutorial & explanation :)
@sachinrv16 жыл бұрын
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
@LeilaGharani6 жыл бұрын
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....
@yuema14955 жыл бұрын
So soothing to listen to your voice and learned some useful skills at the same time !!!
@LeilaGharani5 жыл бұрын
I'm glad to hear that.
@know1squarter3076 жыл бұрын
Genius,Saves CSE and the use of pivot tables....Great video.....
@timosborn732 жыл бұрын
Thanks Leila, very useful & methodically explained.
@Ani_Suv2 жыл бұрын
El Magnifico....That was done so smooothtly..Thank you so much🤗
@rudiklein6 жыл бұрын
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!
@LeilaGharani6 жыл бұрын
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 :)
@rsn29sep5 жыл бұрын
Content is superb, shared with my colleagues, explained with ease
@LeilaGharani5 жыл бұрын
Thank you for your support and sharing the videos!
@Utubemop6 жыл бұрын
Excellent stuff. I just tested it on a random spreadsheet I had open. Thing is.. it had about 100k rows. I was able to extract 36 individual values from one column out of all the datasets but it slowed the spreadsheet down quite a bit. This is probably not the intended use anyways, but a Pivot table might be easier in such cases. But still excellent to know it is possible to be done without Pivot and CSE. I am learning a lot from these and your Udemy courses.
@JeremyMcMahan6 жыл бұрын
I did a similar thing yesterday using PowerQuery. For 100K rows that might be the route to go with. (I used the "Group By" transformation to get the unique list.)
@Utubemop6 жыл бұрын
I have not used PowerQuery yet, but a standard Pivot table was easy to apply here and to extract the individual unique items.
@eriknegron10476 жыл бұрын
If looking for unique values, why not use Sort & Filter > Advanced > Unique Values only. Here is a video that explains it: kzbin.info/www/bejne/nJ6vl6eqj6p7e7s
@Utubemop6 жыл бұрын
Because that needs manual interaction or the use of a macro, which is not wanted often. The specialty here is, that it uses standard formulas that do not need the use of arrays or macros. The list is dynamic and will be updated whenever the sheet is used, whereas the list created in the video you link to is static and needs to be manually updated. The use of the formula explained here can theoretically (if speed would be no issue) create a dynamic list for for a dropdown that has over a million entries if limited to a whole Excel column, there are maybe ways to even expand it. That can be used in a report or dashboard and users can i.e. adapt what they want to see with them having any experience in Excel while the person providing the report does not need to run a macro again to create the list, if new entries are added. The list created in the video is static and will need to be nmanually updated or the macro run again, if another sales person is added, whereas here they just automatically appear in the list and the resulting dropdown will show the new persons automatically.
@LeilaGharani6 жыл бұрын
Glad you like it. Thanks for the feedback on the large data set. Things might speed up a little if we replace the iferror formula with an IF formula only. So if we first check how many unique items we have on the list in a separate cell (maybe with countif & sumproduct) we could then use an IF to check if the number of row results is less than or equal to this - then it should run the array, otherwise it shouldn't. The problem with iferror is it runs the array all the time and with a larger data set, it will probably slow things down....
@harshababu83214 жыл бұрын
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!
@AndrewBennettScience4 жыл бұрын
Countif ?* just changed my life. Thank you! 😁
@symbiosis40934 жыл бұрын
U r just Genius mam...
@mariostudio74 жыл бұрын
This is the second time this video helps me out to solve a problem! I can't thank you enough!
@ahmedfadali40645 жыл бұрын
Thank you very much, I used to use pivot tables, but this formula is worthy to be learned, it would be really handy when creating summary tables (similar to pivot tables) in which columns or rows are needed to be inserted and pivot table doesn't allow to do so.
@doublehd1594 жыл бұрын
I was having so much trouble understanding this concept but you helped me finally get it so thank you!!
@gurmeetsingh-cq6br4 жыл бұрын
Thanks, I applied it in my office. Felt great thanks
@yatinpurohit5 жыл бұрын
Very useful take on alternative to Ctrl-Shift-Enter. Also, very well explained step-by-step.
@LeilaGharani5 жыл бұрын
Thank you. Glad you like it.
@alialtalalwah30274 жыл бұрын
Thanks .. today one skill got gained
@MKapitan4 жыл бұрын
Thank you very much. It was exactly what I looked for.
@LeilaGharani4 жыл бұрын
Glad it helped!
@GeertDelmulle5 жыл бұрын
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.
@LeilaGharani5 жыл бұрын
Thank you Geert! I can't wait either :)
@iziaurrahman5 жыл бұрын
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.
@edtardaguila35995 жыл бұрын
converting to a non cse formula is a stunner for me, thanks a lot
@kennethkretschmer10276 жыл бұрын
Thank you. Your channel has every answer I am looking for to finish setting up my accounting system. Eleven thumbs up.
@LeilaGharani6 жыл бұрын
I'm really happy to hear that! Thanks for the thumbs up :)
@peterzika65353 жыл бұрын
You do far the best thorough videos. Great presentation, explanation and really structural. I am amazed and grateful. Thanks
@alfredmark59003 жыл бұрын
Hi Leila, very much enjoyed new tricks and tips. Great excel brain! I am trying to show staff performance for a unique staff using a line graph. And I am intending to use drop down list to pick a staff and automatically the graph is populated showing the staffs performance over a period of time. Would very much need your brain in this.
@MrDanielduranc2 жыл бұрын
Brilliance, you are the best!! Love it
@danielgarduno80676 жыл бұрын
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!
@LeilaGharani6 жыл бұрын
That's GREAT!
@LucianoPanepucci_EditoraCubo4 жыл бұрын
Thank you. I just had to make my Google Sheet excel compatible used this to replace a simple UNIQUE(A1:A) call. I am sure excel also has a ton of good functions google is missing. I dream of the they they will be fully compatible!
@gauravatrey4 жыл бұрын
Greate way to extract unique items, thank you very much Leila for this video.
@askulasaatwik8thc6445 жыл бұрын
Thanks for easy way of explaining these formulas.
@LeilaGharani5 жыл бұрын
I'm glad you find these easy to follow :)
@engr.mithun74554 жыл бұрын
Wow Tnxxx q Mam I am Frome Bangladesh. I am ur big Fan Mam.
@nallanpradeep6 жыл бұрын
Awesome, I worked on unique drop down list using CSE, but this method is really helpful. Thank you, Leila for Countif criteria "?*".
@LeilaGharani6 жыл бұрын
You're welcome Pradeep. It's fun to look for CSE free versions :)
@TheUnicleo4 жыл бұрын
thanks, Too complicated for me to understand and follow your formulas, you must be genius. 👍 I used VBA Dictionary which was more readable and much easier for me to follow.
@dannyshaw39095 жыл бұрын
I thought about this issue (extracting unique values of dates by month) for half an hour, knowing that I would need a combination of anchored COUNTIF, INDEXMATCH and CSE but still could not figure it out. Thanks for this video!!
@dannyshaw39095 жыл бұрын
It should also be noted that at 11:33, the formula returned "Productivity" due to implicit intersection. Had the formula been in cell E18 and below, it would've returned an error. Once again, thanks for posting this video.
@drsteele47496 жыл бұрын
Way to go Leila. Nice use of INDEX to avoid CSE.
@LeilaGharani6 жыл бұрын
Thanks. It's always fun to look for CSE free formula alternatives :)
@bcgalz4 жыл бұрын
Dear leila thanks for making videos
@karthikb22965 жыл бұрын
I like the channel and your best explanation madam
@LeilaGharani5 жыл бұрын
I'm glad to hear that. It's great to have you here.
@houstonsam61636 жыл бұрын
Very helpful. I frequently learn useful applications of worksheet functions from you, many thanks.
@LeilaGharani6 жыл бұрын
I'm happy to hear that Sam. Thanks for your comment.
@marine_accountant31715 жыл бұрын
This is brilliant formula! Thank you for sharing this!
@pascal.angulu3 жыл бұрын
You are a genius...
@clarehumail5 жыл бұрын
I am real like your teaching, it is very clear and helpful for me. Thank you Leila!!! Btw, I come from Taiwan!!!
@LeilaGharani5 жыл бұрын
That's great. I'm glad you like the tutorials. Greetings to Taiwan!
@BaoPham-sk2hh5 жыл бұрын
This is awesome. I've been trying to write a formula using CSE, but this is way much more efficient! Thanks for the tips! =)
@LeilaGharani5 жыл бұрын
You're very welcome. Glad the tutorial is helpful.
@dadgau6 жыл бұрын
I looked for a tutorial like this for years. Thank you so much.