Excel Dynamic Arrays: Column of Records into Proper Data Set (Excel Magic Trick 1531)
HTML-код
- Опубликовано: 18 сен 2024
- Download Excel File: excelisfun.net...
In this video see how to use Dynamic Array Formulas to convert a column of records into a Proper Data Set. This is a great trick from Bill Szysz.
Entire page with all Excel Files for All Videos: excelisfun.net...
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) • Comprehensive Excel Dy...
Mike, this unwind trick is very nice.
Now, I would use a single Filter-statement for every column in the results table.
Like: filter the second column where the first column equals [name of the header] in the corresponding column of the results table.
Wouldn’t that work as well? (Can’t test it myself, yet).
Yes, indeed, FILTER Around the INDEX will eliminate having to use FILTER twice. I should have thought of that : )
Kevin Carter cooler, yes. But my suggestion eliminates the need to filter out the blank rows, making my suggestion more robust.
Admittedly, I have to repeat the formula for every column in the resulting table - less cool.
OTOH: it’s just a single filter formula per column. Easy as pie (blank rows or not).
ExcelIsFun uhm,... I don’t think I need index, a single filter will do (per resulting column).
I mean: filter the second column, where the first column equals what’s in the header.
Or am I missing something trivial, here? It’s kind of tricky having to do this in the mind only... :-)
@@GeertDelmulle , yes, and I am not that smart so I am not envisioning what you are proposing... Anyone else with Office 365 who can envision the formula and try it?
@@GeertDelmulle Oooooo... No way... I think i get it now.... If it works it is genius : ) But only works when you have Field names,in the first column, which is not usually the case with single column records.
Very grateful for the Excel Dynamic Arrays course
OMG Sequence is the key to do the magic touch,
Thanks Mike and of course to the a genius behind this awesome Formula Bill Szysz
Great video !!😀
Glad it is great! Thanks for the support : )
Glad it is great for you!!!
Wonderful dynamic formula combinations! Thank you Mike & Bill.
You are welcome, Leila : )
@@excelisfun I have seen both video on same topic from Mike is like king of formula and Leila Gharani is like queen of tricks.
Thanks you
Hi Mike
An alternative formulation that you might wish to consider. One could use a bisection search LOOKUP() in place of a direct INDEX() lookup.
That is, instead of
= INDEX( Value, SEQUENCE( M, N ) )
one could use
= LOOKUP( SEQUENCE(M, N), SEQUENCE(M*N), Value )
A bit dumb of me; I didn't say why one might try the alternative. INDEX returns the result as a pattern of references to the original column array whereas the LOOKUP returns a 2D array of values. That allows one to perform aggregations and lookups on the array (preferably as a named formula) as if it were an ordinary range.
3:45 SEQUENCE Function
Thanks Mike :-))))
: ) : ) Most awesome Poet : )
Hey Mike and Bill.. more magic tricks. This is a great one, especially when the data set is not so large and you just want to solve the conversion quickly and directly on the worksheet. Thanks and Thumbs up!
You are welcome, and thanks for the support, Wayne : )
Thanks, Wayne :-))
WOW Mike is amazing
Thanks to you all the Time
and for Excel online Team Bill Szysz
Thanks, Sevag :-)))
If you make the dataset an Excel table and add another set of rows (date, time, product, customer, sales) the spilled array instantly updates with another row. Another nice feature ! Thanks for your fun and clever Excel tricks!
You are welcome, and thanks for the good idea ; )
Great trick. Thumbs up as always!
Great solutions to common data clean up.. Thanks Mike
You are welcome, Matt! Thanks for watching and support : )
thank you so much Mr.Mike I can't wait for the 2 hours video the DAX formulas are actually my confusion i wanna understand them. and i know that there is no one can do that to me except you.
That's amazing to learn new techniques
Glad it is amazing, Vida : )
wow..that is neat!
I agree - Neat : )
Thank you Mike and Bill Szysz :)
You are welcome, Malina :-)))
Go Team!!!!!
Just AMAZING ....thanks mike
Glad it is amazing : )
Revolution In Excel Mike thanlks for sharinggggggg
Thank you, Mike. And, of course, Bill.
Thanks @M. SZ :-)
Wonderful video. Thanks to all at ExcellsFun Team :)
Glad you like it! Go team!!!!!
Great Trick Mike. :) Seems like Geert Delmulle has all already pictured in his mind. Amazing People!!!!! It's nice to hang in here to follow you guys to learn!!!
It is great to hang out on our Online Excel Team!!!!
Very well done sir shown a septecular way o vhange form of data in a table 😇😇😇
: )
Yes sir I saw and I have 2016 Excel please help how can I do
Mike....
What is if you have a dynamic data in a column...
Exactly five rows belongs to one group of data..
What if..we have dynamic group of data..
For example first group of data belongs to 5 rows... second group of data has three rows... So on
Hey Mike could you do a video on how these functions work with tables and how they actually work with tables??
Well... I have already done a few videos about how Dynamic Arrays work when they point to an Excel Table. Here is a playlist of the 15 videos I have made about Dynamic Arrays: ruclips.net/p/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx
In specific, anytime a Dynamic Array Formula Points to an Excel Table Column, when you add a new record to the Excel Table, the Dynamic Array updates : )
is (unique formula) work
What is the shortcut key used when you remove the formatting? (right after you enter the "sequence" formula).
Awesome ...
If I have all the data in horizontal manner and I want to make them in table format then which method i should use.please do let me know
Hello mike
I want to learn excel from basics can you plz suggest me from where to start
Yes, here is my Free Class for Basics (videos, fiels notes, and pratice problems): ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Thanks for your support on each video with a Thumbs Up and Comment, Anuj!!! Thanks for your Sub too.
Mike you are awesome your tricks are just too good to be true. How do you even think of such tricks. This was just way too good. But I just have one question this setup of data for transformation is available through which source
I am sorry, but I do not understand your question.
I mean the data that you used for transformation where is it used I have not seen data in this format
If we delete the original column of records would the newly formatted records stay the same or change?
It is a formula, so if you delete the data, the formula has nothing to work on.
You're right, INDEX function with Dynamic Arrays are just spectacular!!!!
I have been using INDEX for a while, but I never saw in a kind of "Modulo situation" data set. I thought that just Power Query can handle this kind of taks but with dynamic arrays it looks like it can handle perfectly, obviously for smaller data set. Right?
Thanks Mike👍👍
INDEX would be able to handle any array as big as the spredsheet.
Hi there, Can I ask a question about an excel doubt I have (not related to the current video) here or is there another place I need to ask it. (sorry for my ignorance!) Jonathan.
In older version is sequence function isn't available so what can I do? Please give me the right now I have huge data but I am unable to use the same formula on my excel
Did you watch the whole video! At the 06:10 minute mark I showed how to do it in older versions. Also, if you download the Excel workbook I show how...
Thanks for your support, Armaan kadiwala, with a Thumbs Up and Sub : )
Hello Guys, can you help me out how to update new function as like(Unique, filter) in office 365. I'm using office 365. Please help us.
Hi sir, I have not been able to pull the screener data from chartink.com/screener/strong-stocks website into excel. Please guide me.
Great as always.... Could you please explain the last formula to enable us to do without SEQUENCE, & FILTER. Thanks Mike for all your great videos. I must say that whatever quality I learned from your website EXCELISFUN, is not available anywhere.
The formula element in the row argument yields the numbers {1, 2, 3, 4, 5;6,7,8,9,10} and so on... If you take the formula element and paste it into the cells and copy it, you can see the pattern. The full explanation is for another video. I also have many older videos that teach about number incrementing in formulas. Here is a playlist: ruclips.net/p/PLrRPvpgDmw0matjr9DLpc14DJn2OqNgYj
Thanks for the support, Hassan!
Can you help me with a set of matrix data., i need to sort only the red color in cell. but if i sort another column, the 1st column return to it original position.
I like your use of index with SEQUENCE. These two functions will be good friends as Dynamic Arrays are used by a wider audience. However, I raised in a comment (to an earlier video in this series) the concern that Dynamic Arrays cannot be converted to Tables. This video illustrates the basis for my concern. As these tools become available to the general excel user audience and as the tools increase in number from MS they will be used in situations like this one to create and transform data sets. However, MS also with their New Power Tools has created situations, such as the DATA MODEL and POWER PIVOT where conversion to a Table is required. This seems like a huge problem! Or, what am I missing?
I guess it is a problem. But Array Formulas are a solution for the cells. If we needed to do this and bring it into Power Pivot, we would use Power Query, then convert to proper data set. I guess there are just different tools, and maybe someday the Spilled Arrays can be tables...
Of course other options exist. It is impossible to know the future of a data set so it is likely that in the future many data sets will contain spilled arrays and then months or even years later, an attempt will be made (perhaps by another person in the organization) to use it with certain excel tools, or simply convert it to a table, and find out that certain important options were eliminated when the data set was created or transformed using spilled arrays. Standard Have you tried to create a standard pivot table with this data set (table not required). Will standard PTs work?
@@richardhay645 ,I tried PT, but it doesn't see when new items are spilled. I don't think of Array Formulas as Data Sources. But maybe someday MS will allow Spilled Arrays as Data Sources...
Hi Mike, couldn’t we use =unique(transpose(A5:B109)) ?
That would just flip a one way vertical array to horizontal.
arnaud seguin Why not doing it the other way round: transpose(unique(... ?
Only to create the headers of the results table, of course.
BTW: this exercise needs the repetition of the headers in the first column, so you might as well take advantage of that characteristic.
Hey Folks, could anyone help me out with Ms access channel.. I'm not able to find a reliable source to start learning access
I do not know a chnnale that has Access classes for learning by full classes, but Crystal has great Access videos: ruclips.net/p/PL1B2705CCB40CA4CA
@@excelisfun I stumbled upon this channel while looking for access..but things are not properly arranged. It would be great if you can start a series on your channel in a proper way to teach access
such a pity many of us don't have office 365 yet. myself included