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.
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.
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!!!
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.
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👍👍
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: ua-cam.com/play/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx.html 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 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
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: ua-cam.com/play/PLrRPvpgDmw0matjr9DLpc14DJn2OqNgYj.html Thanks for the support, Hassan!
Yes, here is my Free Class for Basics (videos, fiels notes, and pratice problems): ua-cam.com/play/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k.html Thanks for your support on each video with a Thumbs Up and Comment, Anuj!!! Thanks for your Sub too.
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...
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.
I do not know a chnnale that has Access classes for learning by full classes, but Crystal has great Access videos: ua-cam.com/play/PL1B2705CCB40CA4CA.html
@@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
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
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
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 ; )
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 :-))
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.
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.
WOW Mike is amazing
Thanks to you all the Time
and for Excel online Team Bill Szysz
Thanks, Sevag :-)))
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!!!!
Wonderful video. Thanks to all at ExcellsFun Team :)
Glad you like it! Go team!!!!!
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
What is the shortcut key used when you remove the formatting? (right after you enter the "sequence" formula).
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.
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
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, Mike. And, of course, Bill.
Thanks @M. SZ :-)
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.
Revolution In Excel Mike thanlks for sharinggggggg
Thank you Mike and Bill Szysz :)
You are welcome, Malina :-)))
Go Team!!!!!
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: ua-cam.com/play/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx.html
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 : )
That's amazing to learn new techniques
Glad it is amazing, Vida : )
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
Very well done sir shown a septecular way o vhange form of data in a table 😇😇😇
: )
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: ua-cam.com/play/PLrRPvpgDmw0matjr9DLpc14DJn2OqNgYj.html
Thanks for the support, Hassan!
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): ua-cam.com/play/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k.html
Thanks for your support on each video with a Thumbs Up and Comment, Anuj!!! Thanks for your Sub too.
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.
Yes sir I saw and I have 2016 Excel please help how can I do
Great video !!😀
Glad it is great! Thanks for the support : )
Glad it is great for you!!!
Just AMAZING ....thanks mike
Glad it is amazing : )
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.
wow..that is neat!
I agree - Neat : )
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...
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.
3:45 SEQUENCE Function
Hi sir, I have not been able to pull the screener data from chartink.com/screener/strong-stocks website into excel. Please guide me.
Thanks Mike :-))))
: ) : ) Most awesome Poet : )
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 : )
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.
is (unique formula) work
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: ua-cam.com/play/PL1B2705CCB40CA4CA.html
@@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