Dynamic Array & Old Array Formulas to Create Student Classes Report - Excel Magic Trick 1608
ฝัง
- เผยแพร่เมื่อ 13 ต.ค. 2024
- Download Excel File: excelisfun.net...
Learn how to create a report that shows each student by quarter with a list of classes in a single cell. See many of the new Excel 365 Functions like: SORTBY, UNIQUE, FILTER, TEXTJOIN and the ROWS Function. See how we can combine Spilled Arrays formulas and Old School Arrays Formulas together to make a report that automatically updates when new data arrives.
Super Mike! The fun never ends :)) Thumbs up!!
Yes! The fun never ends, even if you watch all 3,300 videos ; )
@@excelisfun That's my goal.. watch (and understand) them all :)) Go Team!!
@@wayneedmondson1065 Even if you watch them all one time, you can watch them again too. I have to watch my own videos sometimes to remember how I did stuff, lol. Some videos I have watched many times.
@@excelisfun Agreed! Watching multiple times multiplies the fun.. and the learning :)) Go Team!!
you can't stop surprising us Mike... thank you so much!
You are welcome so much, Edmundo!!!
Phew! I'm out of breath just watching the video - so much great information in a little space of time! Very interesting section at the end where you allowed for further data to be appended.
Yes, that part at the end, is a construction we have used in array formulas for decades, but the new part is the $F$4# locked Spilled Array inside of ROWS. Glad it was full of infor for you, Claire : )
I'm so happy that i eventually have those New functions !!!
This is so beautiful. Thanks amazing Mike for this EXCELlent video.
Glad you like the new Excel beauty, Syed : ) : )
Dynamic Array is most amazing innovation of Excel in its 20+ years of its evolution.. Cheers :)
It is one of the best ever!!!!
You are awesome. Thanks for this video
You are welcome for the video, Bindhyesh!!!
That's my MAN!!!!! The best of the Best. I was playing about with the previous data using the unique and the xlookup. Thanks Mike. :) :)
Go Team Formulas Rule!!!!!!!!!!!, right John Borg ; ) Glad you like it : ) : ) : ) : )
@@excelisfun One Question, why did you use the unique(sortby..... instead of using just sort(unique...... for the first part??
@@johnborg5419 because I was not use how to access the columns inside the Unique to tell SORTBY how to sort. Do you know how? Maybe I am missing something...
@@excelisfun No No.....I just used the Unique for the Students and Quarters for the array and I had a spilled unique list. Then i wrapped it into the sort, to have a sorted unique list. By the way, I was using the data from video 1607. Am I doing something wrong??
using the data from Video 1607, I have this formula in E4 =SORT(UNIQUE(StudentData013[[Student]:[Quarter]])) and I had a sorted unique list. Very good probabilty that I am missing something not you. : ) : )
SORTBY()? That’s yet another super-useful function us O365 Outsiders don’t have... :-(
BTW: super great video, Mike: it is one continuous string of gold nuggets from the beginning to the end, awesome!
This is the real problem with Microsoft: "O365 Outsiders" Thanks for the new term of shame...
Glad you liked the string of gold nuggets. Geert : ) : )
Another great example of Dynamic Arrays!
I am thinking, if one day, Dynamic arrays can be used within Excel Table, then we do no worry about the "expansion" of new data... Would that be perfect? 😁😁
That would be great! But at this time they will not work in an Excel Table.
Gr8....
=SEQUENCE(COUNTA(b1:b))
Is also fine working for filling 1..2...3... Series....
Yes, but then you are looking at a whole column. Don't you think it is better to just look at cell with spilled array? It seems to be more exactly, straight to the point. The information for how many rows are spilled are exactly in F4#, rather than the whole column. Glad it was Gr8 for you, Tulsidas!!
Yes...
I understand ....
But in Google sheets there is not spilled array ...
Like F4#....
So I can't use this facility...
@@tulsidasjamnani9455 Oh!!! I see. I do not know how to use Google Sheets very well... Luckily we have you on the Team to help with Google Sheets : ) Go Team!!!!
Sir but you will be do it better...
With Google sheets and it's mobile app is also very very easy and user friendly interface...
For users ....
Sir please make some videos on Google sheets.....
Thanks Mike
You are welcome most awesome Dave Bowman : )
Just fantastic stuff here.
Glad you like the fantastic fun, Christopher!!!
Just brilliant!
Glad you like the brilliant fun, Roberto : )
Great instant update with formulas. However, I wish dynamic arrays had automatic expansion feature as Excel table, so that formulas don't need to be dragged
Me too. But Dynamic Arrays currently do not work in Excel Tables... : (
Great trick, Thanks Mr. Mike
You are welcome, Ogwal!!!!
This is great. How would you provide all combinations of two fields in a single column if the fields are unique. For example, say 3 meals (breakfast, lunch, dinner) and 7 days in a week (m,t,w,th,f,s,su) and you want a table of all combinations for a week ([monday,breakfast]; [monday, lunch]; [monday, dinner]; [tuesday, breakfast].... Can you go from two unique lists to a combined list of all combinations from the two unique lists?
Auhhhhhmazing!!!
Nice pronounced, roderick : ) : ) : )
Love it! :)
Glad you love it, pmsocho!!!!!
It's so sad to have great knowledge about array formulas without using them nevertheless good job Mike 👍🙂
I am sorry, DIGITAL COOKING... : ( ... : ( ... : ( ... : (
Thanks again, Mike 😍. Watching your Viedo give me idea to use sort_index with an array.
=UNIQUE(SORT(StudentD[[Student]:[Quarter]],{1,2}))
Great idea, bo!!!!
Great video
Thanks, Phone Excel Time!!!
Hi Mike, thanks for the video, dynamic arrays are more and more the way to go! Have you tried to put the spilled array in an Excel table to see if column H expands automatically when you add data? I cannot try myself because I'm still missing dynamic array formulas...
According to Microsoft, Spilled Arrays and Excel Tables will not work together... : (
Thank you Mike... Could we have some example use of that @ sign before a formula command? For example @MATCH() ?
@ is the implicit intersection operator. so if MATCH were delivering a spilled array and you used @, it would deliver just a single item at that point in the array, rather than the spilled array. I do not know any good use for this, just like in the old Excel, I never had any use for implicit intersection... If you can think of an example, maybe I can make a video...
Maybe this: Excel Is @{"cool","fun","rad"}
@@excelisfun superb video. i used named ranges a lot and am starting to get circular references which I can remove by using @ when using the named range in a formula - probably not the best way of doing it but it works. any videos on calculations with named ranges which are now quite different
2:25 Sequence Function
Nice dynamic arrays :-) when it wil be available in excel 365? We are helpess of dynamic arrays
I do not know, because Microsoft keeps saying "in a few months" for the past one year... But hopefully soon.
@@excelisfun we hope mike..we can not practise dynamic arrays exemples
Thank you:-))
Go Team!!!!
Cool
Glad it is cool for you, Techie Soumalya!!!!
Well done, Mike! But it’s immensely frustrating that trying to use dynamic array on a spilled array generates an error. This fails =FILTER(SD[Classes],SD[Student]=F4#) even though we all think it shouldn’t. Maybe this is one reason MSExcelTeam is taking so long to roll out dynamic arrays. On another note, when I create a formula I know is correct except for the final parenthesis, I usually just quickly press Enter and then Enter again to accept the suggested correction - beats typing Shift9 to get er done.
This fails in any formula; SD[Student]=F4# because the two arrays are vertical and NOT the same dimensions. This has nothing to do with Dynamic Arrays. I mentioned this in the video. You can take SD[Student]=F4# and do this: SD[Student]=TRANSORM(F4#) and then use MMULT, but I could not get that to work in FILTER either...
@@excelisfun Thanks, Mike. I had tried all kinds of techniques with TRANSPOSE and MMULT and ended up frustrated too. Oh well.
@@drsteele4749 I am pretty sure it is because FILTER delivers a list of values and then we have the weird array in the filter. I bet there is a way, but I caould not find it yet, and it probably is crazy complicated. This is exactly why they invented DAX. DAX can solve many problems, like this one, much easier than any other method.
Hi Mike, How would you do it in excel 2019 Dynamically. Please help! Thank you in advance
There are no spilled arrays in Excel 2019. Only Microsoft 365
@@excelisfun so there is no way you could similar in excel 2016 and or 2019? here has to be a way. You're Excel grandmaster. please Help!
Great solution but I would rather "drag it down"!!
That'll work : ) Thanks for stopping by, Richard Hay!!
I am currently working on a similar software but I am facing a challenge. All students are reading the same core subjects but with different electives subjects. how do I import the class mark, exams mark and position from the master sheet onto the report? Can I get ur email address so I can share files with u for help?
how can I contact you ?
Right here.
@@excelisfun not going to talk business in youtube comments ;D