Oz. Breathe in Excel, exhale anxiety - I choked on my breakfast with that unexpected comment. Hard to meditate while you are laughing. The music and the bubbles were soothing - but your voice perfect
Another excellent video, including a tip from another cool guy that I like! Dynamic arrays are awesome, and I have been replacing some tricky drop-down menus based on OFFSET and counts by them... Much simpler!
Great relaxation session. Great application of dynamic arrays. It would be even greater if Microsoft allowed to enter that formula directly into Data Validation window, instead of typing it into a cell and then referencing the cell.
I am always ready to learn from You Oz. I am however always unprepared for the amount of knowledge and wisdom you will present We also got a bonus meditation session too. Damn. Oz of all trades I’m working on a lambda function for a searchable sorted unique list for drop downs with the option to add letter headers. I’ll share when I’m done
@@OzduSoleilDATA Here it is DVLIST It returns a sorted, unique list of search results with or without character headings or an error value. It takes 3 arguments. It uses the current cell's contents as the search criteria. =DVLIST(Array, If_Empty, Add_Headings) Array is required, if_Empty & Add_Headings are optional arguments but must include commas 1. Array: the vertical array to find search results in, 2. [If_Empty]: no search results value; ""☛ default, 3. [Add_Headings]: TRUE or 1=add character headings OR FALSE or 0=exclude☛ default) =LAMBDA(Array,If_Empty,Add_Headings, LET(Arr, Array&"", Text, CELL("contents"), AddHeadings, IFERROR(--(Add_Headings)=1, 0), Condition, ISNUMBER(SEARCH(Text, Arr)), ErrorScan, SUM(--(Condition))=0, NoHead, FILTER(IFERROR(--(Arr), Arr), Condition), ValCt, ROWS(NoHead), Seq, SEQUENCE(ValCt*2), Headings, UPPER(LEFT(NoHead)), YesHead, IF(Seq
Hi Oz. Way cool! Actually, you don't even need the TRANSPOSE function. I duplicated your table and named it Exams. The following in B13 will spill the names sorted, horizontally and filtering out whatever is the first column name in the table (in this case "Exam"): =SORT(FILTER(Exams[#Headers],Exams[#Headers]Exams[[#Headers],[Exam]]),,,TRUE). You just need to remember to add the TRUE or 1 argument for [by_col] inside SORT. Not better or worse, just different. Thanks for sharing this. Looking forward to checking out the Meetup link :)) Thumbs up!!
Excel on 🔥🔥🔥🔥🔥🔥🔥 blowing developers minds LOL when you said: *I wasn't* (ready for this) @ 00:04 😂😂😂🤜💥🤛 And you are also a DJ, master!!! Congratulations on your playlist!!! I remembered when I danced break dance! Hahahaha...
It's like the Ninja blender, put all this "good stuff" in mix it up and pour it over ice. Cooled down and get the ice cream to cool down more. Excel-ellent example Oz and Faraz!
Here is an alternative, Dropdown list: =SORT(DROP(Table1[#Headers],,1)) Calc outputs: =MIN(INDEX(Table1, ,MATCH(K8,Table1[#Headers],0))) (K8 is the dropdown list output)
Also if you don't need to sort the drop down list you can create a named range of the header and then reference directly in the data validation dialog, and no you don't need to transpose it =DROP(Headers,,1) (Headers is my named range of the table header) If you add new columns to the table the drop down grows with it.
Oz. Breathe in Excel, exhale anxiety - I choked on my breakfast with that unexpected comment. Hard to meditate while you are laughing.
The music and the bubbles were soothing - but your voice perfect
Grainne! 🤗
Now that was HOT! two of my favourites....XLOOKUP and DYNAMIC ARRAYS.
This guy is so underrated. My new religion
Thank you so so so much.
You're some kind of magician, aren't you?
Awesome as always!
Thank you
GREAT, I am on fire ... Thanks Oz.
I’ve always done this with index match formulas, definitely going to give this a shot. Thanks!
Yes, that was fantastic!! I appreciate you. Thank you!!
Thank you 🙏🏼
My mind has been blown! Hard!
Uh oh! I should make helmets available in case minds are blown. 😁
WOW, you are amazing, GOD bless you
Thank you, Hani.
God bless! 🙏
This is super juicy! Love your uploads always the latest stuff! Thank you
HEY HEY!
People are really getting creative with dynamic array functions, thanks for the video awesome as always!
You are so welcome!
Go forward! Be creative. 💥😎
Crackin good time. You're on fire Oz 🔥💚
HEY HEY! Gotta have a good time. 🕺🎉🧨
You're the only Excel themed YT channel i can watch while having breakfast lol...
Learning while having fun....Thanks Done Oz
Very good morning! I'm glad to have shared breakfast with you. 🙏
Hectic video, that was insane!
😊🙏
Oz this is Absolutely amazing video ....thanks a lot
You are so so welcome. 😁🤗
I love your teachings method! Cheers from Brazil and I would like to be challenged someday 😢❤
Love it! I've never been very good at meditating - whenever I try I'm usually just thinking about Excel, anyway!
😁🤗
Dynamic solution, indeed! :)
YES YES!
Man, your videos are amazing! I'm very happy I found your content! Great stuff! Keep it up!
THANK YOU!
I have to make a new video very soon.
I’m loving these new dynamic area functions, it’s like a whole new excel. And when is your meditation channel going live Oz🤣😁
Meditation channel? Hmmmm ... 🤔😁
Another great tutorial, Oz! Pure magic!
🤗
Great video!
I would give more than one thumb up
And I would accept them. 😊👍🏽
Thank you, Sten.
AMAZING!!!!!!
Sensacional...Thank you OZ!
Luciano, you are so welcome.
Thanks for the fascinating tips. I would expect you to share the exercise files as well.
The file is there for downloading
Hi Oz, very late to the party here but this was awesome, thank you for sharing 😀
Another excellent video, including a tip from another cool guy that I like! Dynamic arrays are awesome, and I have been replacing some tricky drop-down menus based on OFFSET and counts by them... Much simpler!
You've got me very curious. I want to see the OFFSET trick. 🧐
Great relaxation session. Great application of dynamic arrays. It would be even greater if Microsoft allowed to enter that formula directly into Data Validation window, instead of typing it into a cell and then referencing the cell.
You can if you create a named range for the header and you don't need to sort it, I just gave an example in the comments
I am always ready to learn from You Oz. I am however always unprepared for the amount of knowledge and wisdom you will present
We also got a bonus meditation session too. Damn. Oz of all trades
I’m working on a lambda function for a searchable sorted unique list for drop downs with the option to add letter headers. I’ll share when I’m done
Patrick! Please do share. Those Lambdas still kinda freak me out.
And bring some ice cream. 🍦🍨🍧
@@OzduSoleilDATA most definitely. It requires many of the dynamic array formulas. I’ll share it soon
@@OzduSoleilDATA Here it is
DVLIST
It returns a sorted, unique list of search results with or without character headings or an error value.
It takes 3 arguments. It uses the current cell's contents as the search criteria.
=DVLIST(Array, If_Empty, Add_Headings)
Array is required, if_Empty & Add_Headings are optional arguments but must include commas
1. Array: the vertical array to find search results in,
2. [If_Empty]: no search results value; ""☛ default,
3. [Add_Headings]: TRUE or 1=add character headings OR FALSE or 0=exclude☛ default)
=LAMBDA(Array,If_Empty,Add_Headings,
LET(Arr, Array&"", Text, CELL("contents"), AddHeadings, IFERROR(--(Add_Headings)=1, 0),
Condition, ISNUMBER(SEARCH(Text, Arr)), ErrorScan, SUM(--(Condition))=0,
NoHead, FILTER(IFERROR(--(Arr), Arr), Condition), ValCt, ROWS(NoHead), Seq, SEQUENCE(ValCt*2),
Headings, UPPER(LEFT(NoHead)), YesHead, IF(Seq
@@patrickschardt7724 😲 I'm gonna have to take some time with this. WOW!
@@OzduSoleilDATA thanks. Feel free to ask questions
Hi Oz. Way cool! Actually, you don't even need the TRANSPOSE function. I duplicated your table and named it Exams. The following in B13 will spill the names sorted, horizontally and filtering out whatever is the first column name in the table (in this case "Exam"): =SORT(FILTER(Exams[#Headers],Exams[#Headers]Exams[[#Headers],[Exam]]),,,TRUE). You just need to remember to add the TRUE or 1 argument for [by_col] inside SORT. Not better or worse, just different. Thanks for sharing this. Looking forward to checking out the Meetup link :)) Thumbs up!!
Ah! Ok, Wayne. That's good to know. Thank you for the insight. 👍🏽💥💥💥
Excel on 🔥🔥🔥🔥🔥🔥🔥 blowing developers minds
LOL when you said: *I wasn't* (ready for this) @ 00:04 😂😂😂🤜💥🤛
And you are also a DJ, master!!! Congratulations on your playlist!!! I remembered when I danced break dance! Hahahaha...
OH LORD! You're taking me back to the days of the big piece of cardboard in the street.
I did a little breakdancing myself. 🤭
@@OzduSoleilDATA *Wowww!! That's what I'm talking about, mannnnn!!! Dancing in a cardboard in the street was so good!!! 😂😂😂😂 Good times!!! Awesome!!!*
This is magic
🙏🏼
Wello done Oz, I liked the meditation break ! this solution is quite easy once you know it, compared to the traditional way with INDEX etc....
Yeah, man! No INDEX or anything too weird. 🙌💥
Josette must be watching your videos she smart as hell
Wow!!!!
Yes! WOW! 💥
It's like the Ninja blender, put all this "good stuff" in mix it up and pour it over ice. Cooled down and get the ice cream to cool down more. Excel-ellent example Oz and Faraz!
DOUG!!!!
You've got the idea. Ice. Ice Cream. Turn up the AC.
Whatever it takes to keep from overheating. 😁
I JUST BECAME A JEDI after this.
Here is an alternative,
Dropdown list: =SORT(DROP(Table1[#Headers],,1))
Calc outputs: =MIN(INDEX(Table1, ,MATCH(K8,Table1[#Headers],0))) (K8 is the dropdown list output)
Also if you don't need to sort the drop down list you can create a named range of the header and then reference directly in the data validation dialog, and no you don't need to transpose it
=DROP(Headers,,1) (Headers is my named range of the table header)
If you add new columns to the table the drop down grows with it.
Oil = Black Gold
Excel = Green Gold
Amen!
When you went to the Meditation I thought you were going to then tell us how to use as a custom data type. I was wrong.
😞 Oops!
Hopefully you did get something useful from the video.
I had learnt similar tricks from #LeilaGharani few weeks ago - she is also awesome
You ever get that money back?
Nope. Ilgar and Faraz stopped answering their phone. 😄