Excel Dynamic Arrays: Column of Records into Proper Data Set (Excel Magic Trick 1531)

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.

[GeertDelmulle]

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).

[fernando]

Very grateful for the Excel Dynamic Arrays course

[sevagbarsoumian]

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

[RobMichaels]

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!

[LeilaGharani]

Wonderful dynamic formula combinations! Thank you Mike & Bill.

[sevagbarsoumian]

WOW Mike is amazing
Thanks to you all the Time
and for Excel online Team Bill Szysz

[ismailismaili]

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.

[wayneedmondson]

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!

[peterbartholomew]

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 )

[sachinrv]

Wonderful video. Thanks to all at ExcellsFun Team :)

[m.sz.]

Thank you, Mike. And, of course, Bill.

[mattschoular]

Great solutions to common data clean up.. Thanks Mike

[johnborg]

Great Trick Mike. :) Seems like Geert Delmulle has all already pictured in his mind. Amazing It's nice to hang in here to follow you guys to learn!!!

[vida]

That's amazing to learn new techniques

[harishpaldhir]

Very well done sir shown a septecular way o vhange form of data in a table 😇😇😇

[josebetancourt]

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👍👍

[QuickMadeUpName]

Hey Mike could you do a video on how these functions work with tables and how they actually work with tables??

[AweshBhornya-ExcelforNewbies]

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

[WynSee]

What is the shortcut key used when you remove the formatting? (right after you enter the "sequence" formula).

[rrrprogram]

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