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).
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.
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!
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!
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.
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!!!
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.
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👍👍
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
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: ru-vid.com/group/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 : )
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
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.
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: ru-vid.com/group/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...
Yes, here is my Free Class for Basics (videos, fiels notes, and pratice problems): ru-vid.com/group/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k Thanks for your support on each video with a Thumbs Up and Comment, Anuj!!! Thanks for your Sub too.
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...
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.
@@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