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.
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.
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!
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!!
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.
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!
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.
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!
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!
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))))) }
Best video on RU-vid. 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.
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,"")
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.
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)
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.
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
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.
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.
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. 👍🏻🤓
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
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.
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.
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
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....
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 :)
@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.
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...
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..
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.
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.)
If looking for unique values, why not use Sort & Filter > Advanced > Unique Values only. Here is a video that explains it: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-fmLfttZEHEY.html
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.
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....
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
After few attempts I was Able to apply your tutorials, and I thank you for that. I just have another question, How to make it appear in an alphabetical arrangement, So that if I insert in between the list it will be arrange automatically in the list.
This is amazing! If you remember I had asked this same question few months ago but I think it didn't described the requirement well enough. Thank you very much for this insightful video. Keep Rocking
