Oh man Mike this is incredible, thank you, you are pushing the envelope in Excel as always.

Absolutely brilliant. I watched your old number incrementor videos and they are the foundation for these tricks. Thank u Mike for this amazing video 📹

Great video, thanks Mike. I love the power of the new dynamic arrays and discovering ways to use them to get the best results.

And now we can do it with VStack in a very easy formula. The Dynamic array team at Microsoft are developing great tools at a rapid pace

This is a very creative solution, something I have been looking for, but I will still use the Power Query in most of my situations. Thank you Mike and thanks for Bill too!

This is what I was looking for. Now I just have to apply it. Thank you so much Mike!!

Amazing tutorial....very explicit as always. Thanks for sharing

The power of excel... love it... thanks Mike and Bill!

Boom!Absolutely Awesome Tutorial Love Learning How To Use These New Dynamic Array Formulas... Thank You Mike And Thank You Bill :):):)

How do you always know what I need, my hats off to you Mike, thanks!!!

insane tutorial! i was disappointed with the lack of flexibility when it comes to conditional formatting and dynamic arrays but I can solve my problem using this solution instead! your videos saved me lots of trouble!

Thank you so much!! I‘ve been searching for a solution for quite some time to avoid doing a task manually and this worked perfectly! Thanks again!

Nothing like another useful Excel video to start the day, thanks Mike!

This is awesome! It's exactly what I was searching for.

That's mind-blowing. I have been doing this using copy paste. Thanks mentor

Wow! What a great way to accomplish this. Thank you for the video.

Thanks Bill and Mike for sharing. Nice work...

Excellent content as per usual Mike! Small suggestion: include the LET function, it pleases the eye ;)

Seriously amazing, thank you!

I needed to do this the other day and used a non-dynamic array method. It's a lot harder than it should be. Can't help but think Microsoft should release a function that can do this with ease.

Awesome trick and Excellent explanation.

Now that's really spectacular by Szsysz and you, Mike! Next: contact MS Excel Development Team and beg them to create a native function that stacks arrays.

You're a freaking genius. RESPECT.

Awesome! I love the # sign. It makes thing a lot easier :)

As usually amazing, thanks a lot!

I have been searching for a while on how to do this. thank you.

Excellent Solution.. thanks!!

Thanks a lot for this solution !

Simply marvelous!

Nice! I use mod and quotient a lot to get a repeating sequence or repeating numbers that are incremented to put in the index function. Didn't know about the 1+1\x trick, that's neat!

Simply amazing

Exactly what I needed.

Excelente, me funcionó muy bien. ¡Muchísimas gracias!

Great trick as always :)

Incredible Thank You Sir

Excellent as always...

Thanks Mike 👍👍

Thank you Mike. I was happy to use this formula but there is an issue whereby one of my column that only has one entry is duplicated to reflect the number of rows of the results and when a column is blank, it returns those blanks to the max no of rows in the set as well. Is there a way to fix this?

Hi Mike (and Bill S.).. wild stuff.. thanks for this formula solution to append multiple lists into a single list.. magic! Thumbs up for ExcelIsFun!! PS - I'm still locked out of commenting on EMT 1645.. strange!

@ExcelIsFun Thanks Mike, this was a perfect solution to an Excel template I recently built. I wanted Text Validation with a drop-down list based on the Distinct entries found in one column and user-generated entries in another. This + a SORT() generated the list for validation I was looking for.

Thanks Mike!!!

Thanks Mike. This is a boost for my upcoming day. :)

incredible!!

Very cool, you should do a video showing the LET formula so that you do not have to do the filter twice.

Good video. How do you check if the value of a cell comes from a row that has two or more items right in its columns?

Thanks a lot.

Hi Mike, i'v seen an excel function that converts nxn matrix into one column, just one click but I forgot the function name and since then I am still looking for it.

Oooh. Tres cool. I don’t think I have a use for this, but (funny thing about dynamic arrays) new use cases seem to pop up when I learn more about how to employ DA’s.

(1). Boss, please explain how to read pdf file and work on it by importing it first and then do some transformation before loading and reporting. (2) Waiting for video playlist on all cubes functions. Thank your for all your excellent and awesome videos .

Cool. Wouldn't it be great if you could declare the choose function result at the beginning as a variable? That's surely the next leap for the Excel team.

Thanks a lot sir

Hi Mike, one question about this - if one of the table names we start with has only 1 name, the choose formula repeats the 1 name multiple times instead of showing "#N/A", which throws off the index formula.. how would you tackle this? In my case, i cant use unique because if it legit duplicates, i would like to pick it up.

Speechless...where can this trick be useful🤔??

Thanks 😍🥰

Thanks Mike for such clear, expert-level explanations. :-)) One note about formulas using TEXTJOIN: the length of the resulting text cannot exceed 32757 characters. A formula with the FILTER function has no such restrictions. But it has a different limitation. None of the tables can be one-row table. If so, then enter the # N / A error value in the second line. (easiest trick) Under certain conditions, we can also combine multi-column tables / ranges. Of course, these are academic discutions because I would choose PQ anyway :-)))

WOW! 🧠

I've pulled up this video so many times now and the starting talk about this is just getting funnier every time lol

Really informative. Thank you. I think append should be easier. In Google Sheet we can do the same with an easier way like this: ={range1;range2;range3} I don't know why in Excel we can not do it like this!

GIVE IT UP FOR EXCELLS FUN CHANNEL!!!!

This is great. But can you explain how to specify which record belong to which column. When you append two column, in a seperate column specify which column does a record came from?

epic, epic, epic

Sir I love you, lol.

Any video to produce the same result but without duplicating same cell content like "Gigi"?

Hey Mike, this becomes MUCH easier using the LET function. I just merged them all together and then parsed it! =LET(TheNames,TEXTJOIN(",",FALSE,Names01,Names02,Names03),TRIM(MID(SUBSTITUTE(TheNames,",",REPT(" ",LEN(TheNames))),((ROW(INDEX(A:A,1):INDEX(A:A,LEN(TheNames)))-1)*LEN(TheNames))+1,LEN(TheNames))))

My Friend excelente video. How can i send you a direct mensage with my method to append list without tables and long formulas

Very Great Video but Hard for me.

Had to rewatch this video. Being able to stack vectors or -conversely- flatten arrays is very important in numerical mathematics (as well). I remember: in MATLAB flattening an array A is easy: just say: "A(:)" and it gets turned into a single column vector. I wish Excel had something as simple as that (why not "F3#:", or "Array:", where "Array" is a defined name? [mind the colon]). And no, I respectfully disagree with (The Great) Bill Szysz: I would not use PQ-M for this: it is important to have this as a dynamic function in Excel "Classic" because numerically speaking it sits somewhere in the middle of a chain calculation (e.g. an even greater single-cell-calculation). BTW, mashing formulas together is great fun, but since the advent of the LET function, I 'hate' to see them Old School Style: not intuitive to read at all, and plenty of redundancy and thus computational inefficiency. So I made this LET version of it - IMO that is easier to read and understand: =LET( Array,CHOOSE({1,2,3},Names0113[Names],Names0214[Names],Names0315[Names]), NrRows,ROWS(Array), NrColumns,COLUMNS(Array), NrCells,NrRows*NrColumns, RowIndexVector,MOD(SEQUENCE(NrCells,,0),NrRows)+1, ColumnIndexVector,SEQUENCE(NrCells,,0,1/NrRows)+1, StackRAW,INDEX(Array,RowIndexVector,ColumnIndexVector), StackClean,FILTER(StackRAW,NOT(ISNA(StackRAW))), StackClean)

When creating the table, why do I get "an array value could not be found" error? I made the named ranges and all, no empty cells, confused :(

Sick. And you thought you were a power-user?

Update: The new LET function (only Office 365 Insiders, so far), it will change the foundation of formula writing in excel, bringing structure to complex long formulas, now much easier to share and understand. Can not wait the amazing video tutorials Mike is going to post. So this is how it looks for a complex task like this one. =LET(txtjn,TEXTJOIN("|",1,Names01,Names02,Names03),lenlst,LEN(XLOOKUP(TRUE,Names03"",Names03,,,-1))-1,mida,UNIQUE(SEARCH("|","|"&txtjn,SEQUENCE(LEN(txtjn)-lenlst))),midb,UNIQUE(SEARCH("|",txtjn&"|",SEQUENCE(LEN(txtjn)))),MID(txtjn,mida,midb-mida)) Note: variable names arguments do not accept numbers.

vstack can do this now

If we have =TEXTJOIN("-",,Names01AN[Names],Names02AN[Names],Names03AN[Names]) in cell P2 we can write bellow formula to reach same result. =TRIM(MID(SUBSTITUTE(P2,"-",REPT(" ",LEN(P2))),(SEQUENCE(LEN(P2)-LEN(SUBSTITUTE(P2,"-",""))+1)-1)*LEN(P2)+1,LEN(P2)))

I love how literally everything in Excel is: "So, you want to put A before Z? Simple. Just write 57 lines of code in Python, test it for 3 weeks with a team of at least 10 phds, refer to the Excel 98 manual v2.45 on pages 1,549-2,328 and then run tests on a piece of A5 paper." Meanwhile, there is now a button to remove image backgrounds in Excel 🤦‍♂🤦‍♂🤦‍♂🤦‍♂

This stuff is very clever but entirely pointless. IMO excel is obsolete for anything other than a data presentation tool. These manipulations should be done in sql.