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!
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.
@@excelisfun If PQ can do it in a snap and dynamic-array functions can be coerced into doing it, then MS can surely create one as simple as a pimple, I'll be bound.
Sorry to be so offtopic but does anybody know a tool to log back into an Instagram account?? I was dumb forgot the login password. I would appreciate any help you can offer me.
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!
You are welcome for this maybe we will use it once in a while but the rest of the time we use Power Query. We are lucky to have Bill Szysz on our Team : )
Me too! The dynamic array functions are great for running complex macro-free workbooks but difficult to use the results from multiple sources in pivot tables as you can't use the spill functions in tables as part of a data structure. This method has worked for me on a workbook I'd been struggling with for a while. Good work Bill Sycsz and Excelisfun! Goodbye Volatile functions we won't miss 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.
@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 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 :-)))
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.
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!
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?
@@excelisfun I did learn from this too. I never knew that the columns in the index argument sees only the integer. That was GREAT!!!. Thanks again. :) :)
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!
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!
(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 .
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.
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))))
That is too funny, I posted a similar formula in LET yesterday on Mr Excel RUclips video : ) Go Team!!!! LET is a great new addition to our Excel tool kit.
The function below doesn't require any named ranges. When pasted in a cell, the only thing the user would need to adjust is the ranges in the first row which define the TEXTJOIN string. A1:A5 , B1:D1 , A12:C16 =LET(MyString,(TEXTJOIN("|",TRUE,A1:A5,B1:D1,A12:C16)), MyCount,LEN(MyString)-LEN(SUBSTITUTE(MyString,"|",""))+1, MyLen,LEN(MyString), TRIM(MID(SUBSTITUTE(MyString,"|",REPT(" ",MyLen)),(TRANSPOSE(SEQUENCE(1,MyCount))-1)*MyLen+1,MyLen))) Then you can always throw in SORT)UNIQUE(VALUE( in front of TRIM or at the beginning of the function. Google Sheets solution is better on this one. It also has =FLATTEN(
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.
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)
@@excelisfun Just to be clear Mike, this is just a LET-rehash of the construct you bult in the video. Thus it’s nothing new (unlike all those other formulas by other commenters on stacking vectors and even on stacking arrays (other video)). I like that construct most (preferably in this LET-format). I will use it to flatten arrays (into a single column) -so, I’ll start from there- and of course I’ll build it into a UDF using LAMBDA and call it FLATTEN. I think it is time we start compiling our own libraries of functions and gather them into one master template we can start from to build our models. Just image the sheer computational ease and power that would give us...
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, 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.
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.
If you have your named ranges you can, at least in office 365, just append the ranges by " =namedRange1:namedRange2:namedRange3 " etc. instead of using the choose-function. This way you won't have the duplicates, but zero values on the 'missing' parts of the arrays.
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.
A couple of years ago I made an entire simulation of the water level in a Waste Water Treatment tank and used Solve --> Genetic Algorithm to tune the pump firing sequence. Changing the Waste Water Treatment Plant parameters and running the solver again would result in pump firing sequences for the new plant. But you could also hunt for it manually by looking at the graph and dashboard. In Excel... No Macros...
![Tee Grizzley - Detroit (Feat. 42 Dugg) [Official Video]](http://i.ytimg.com/vi/aeKfTtt0x14/mqdefault.jpg)
Oh man Mike this is incredible, thank you, you are pushing the envelope in Excel as always.
You are welcome, but these is an easier way with the even newer function HSTACK: ruclips.net/video/dHySYFz4Dzc/видео.html
@@excelisfun OK thanks Mike, I will transfer my state of awe and wonder to this method!
Amazing tutorial....very explicit as always. Thanks for sharing
You are welcome! I am so glad that you can boomerang back often to find useful Excel topics, Nono!!!! : )
@@excelisfun Yes sure...I make reference to your videos a lot
@@nonoobott8602 : ) : )
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
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!
Glad to help!
Absolutely brilliant. I watched your old number incrementor videos and they are the foundation for these tricks. Thank u Mike for this amazing video 📹
You are welcome, Nader!!!! I love that you are watching and studying so many videos. You are becoming a true Excel Master : ) : ) : ) : )
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.
I TOTALLY agree!!!!! If Only: {Names01;Names02;Names03}
@@excelisfun If PQ can do it in a snap and dynamic-array functions can be coerced into doing it, then MS can surely create one as simple as a pimple, I'll be bound.
yeah {Names01;Names02;Names03} tried this as soon as i learnt about dynamic arrays :( So good to find this video though
Sorry to be so offtopic but does anybody know a tool to log back into an Instagram account??
I was dumb forgot the login password. I would appreciate any help you can offer me.
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!
You are welcome. Check out this video also: ruclips.net/video/dHySYFz4Dzc/видео.html
Seriously amazing, thank you!
You are seriously welcome!!!
This is awesome! It's exactly what I was searching for.
Glad this helps!
This is what I was looking for. Now I just have to apply it. Thank you so much Mike!!
The power of excel... love it... thanks Mike and Bill!
Go Team!!!!!!
You're a freaking genius. RESPECT.
Glad you like it, in a respectful way!!
Great video, thanks Mike. I love the power of the new dynamic arrays and discovering ways to use them to get the best results.
Glad you like it, Roy! Thanks for stopping by in the comments : )
Nothing like another useful Excel video to start the day, thanks Mike!
Yes, starting the day with Excel is as important as coffee ; )
Yes it is!
How do you always know what I need, my hats off to you Mike, thanks!!!
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!
You are welcome for this maybe we will use it once in a while but the rest of the time we use Power Query. We are lucky to have Bill Szysz on our Team : )
Excellent Solution.. thanks!!
I have been searching for a while on how to do this. thank you.
Yes, lucky we have Bill Szysz around to help us out : )
Me too! The dynamic array functions are great for running complex macro-free workbooks but difficult to use the results from multiple sources in pivot tables as you can't use the spill functions in tables as part of a data structure. This method has worked for me on a workbook I'd been struggling with for a while. Good work Bill Sycsz and Excelisfun!
Goodbye Volatile functions we won't miss you!
That's mind-blowing. I have been doing this using copy paste. Thanks mentor
Simply amazing
Glad you like it!
Thanks a lot for this solution !
Awesome trick and Excellent explanation.
Glad you like the trick from Bill Szysz and explanation too : )
Incredible Thank You Sir
You are welcome!
Thanks Bill and Mike for sharing. Nice work...
You are welcome, Matt!!!! We are lucky to be able to hang out with Bill Szysz : ) : )
Awesome! I love the # sign. It makes thing a lot easier :)
Yes#
Boom!Absolutely Awesome Tutorial Love Learning How To Use These New Dynamic Array Formulas... Thank You Mike And Thank You Bill :):):)
Boom!!!!!! We have a great Team!!!!!
Wow! What a great way to accomplish this. Thank you for the video.
You are welcome, Dennis D!!!! Lucky we have Bill Szysz on our Team : )
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.
GIVE IT UP FOR EXCELLS FUN CHANNEL!!!!
Simply marvelous!
Glad you like it, in a marvelous way ; )
Speechless...where can this trick be useful🤔??
Excellent content as per usual Mike!
Small suggestion: include the LET function, it pleases the eye ;)
@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.
It is much easier now with the latest M 365: ruclips.net/video/E5V1ojAXEHk/видео.html
Very cool, you should do a video showing the LET formula so that you do not have to do the filter twice.
Exactly what I needed.
Excelente, me funcionó muy bien. ¡Muchísimas gracias!
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 :-)))
Thank you, Bill Szysz for being an amazing Teammate!!!!!!!
@@excelisfun mind-blowing Mike ! #again ! I agree with Bill here, very clear and expert explanation! I needed this a week ago! #GoTeam !!!
@@spilledgraphics Go Team!!!!!!
I've pulled up this video so many times now and the starting talk about this is just getting funnier every time lol
Glad it gets funnier each time : )
Excellent as always...
Glad it is EXCELlent for you, Khan : ) : )
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.
Ya, that would be as cool as DAX : )
Thanks Mike 👍👍
Great trick as always :)
Go Team!!!!!!
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!
Yes, I answered someone elses comment and spent significant timm and when I posted it went into no-wear land... I still can't figure it out : (
incredible!!
Glad you like it!!!
WOW! 🧠
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?
As usually amazing, thanks a lot!
You are welcome a lot, Maan!!!!
Thanks Mike. This is a boost for my upcoming day. :)
Yes, boosts are good : ) : ) : )
@@excelisfun I did learn from this too. I never knew that the columns in the index argument sees only the integer. That was GREAT!!!. Thanks again. :) :)
@@johnborg5419 Yes, but try: 2.99999 then 2.999999, then 2.9999999 ; )
@@excelisfun Tried it!! But why does it change to the 3rd column on the 7th 9?? It's still not a 3??
@@johnborg5419 I have no idea.
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!
Cool! Glad you play in Excel a lot and like the Divide by ROWS : ) : )
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!
That is so amazing: How Easy it is with Google Sheets!! I hope Excel Team adds an append function soon : )
Thanks Mike!!!
You are welcome, Duy!!!!
(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 .
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.
Right! I have only needed to append columns with a formula a few times in a few decades. But it is good to know it CAN be done : )
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))))
That is too funny, I posted a similar formula in LET yesterday on Mr Excel RUclips video : ) Go Team!!!! LET is a great new addition to our Excel tool kit.
The function below doesn't require any named ranges. When pasted in a cell, the only thing the user would need to adjust is the ranges in the first row which define the TEXTJOIN string.
A1:A5 , B1:D1 , A12:C16
=LET(MyString,(TEXTJOIN("|",TRUE,A1:A5,B1:D1,A12:C16)),
MyCount,LEN(MyString)-LEN(SUBSTITUTE(MyString,"|",""))+1,
MyLen,LEN(MyString),
TRIM(MID(SUBSTITUTE(MyString,"|",REPT(" ",MyLen)),(TRANSPOSE(SEQUENCE(1,MyCount))-1)*MyLen+1,MyLen)))
Then you can always throw in SORT)UNIQUE(VALUE( in front of TRIM or at the beginning of the function.
Google Sheets solution is better on this one. It also has =FLATTEN(
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.
That is too funny, I posted this formula in LET yesterday on Mr Excel RUclips video : ) Go Team!!!! LET is a great new addition to our Excel tool kit.
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.
You are welcome a lot, Shipping Li : ) : )
Sir I love you, lol.
Love is Good! Excel is fun lol
Any video to produce the same result but without duplicating same cell content like "Gigi"?
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)
Let's have fun and efficiency with Geert's fabulous LET formula!!!!!! Go Team!!!
@@excelisfun Just to be clear Mike, this is just a LET-rehash of the construct you bult in the video.
Thus it’s nothing new (unlike all those other formulas by other commenters on stacking vectors and even on stacking arrays (other video)).
I like that construct most (preferably in this LET-format). I will use it to flatten arrays (into a single column) -so, I’ll start from there- and of course I’ll build it into a UDF using LAMBDA and call it FLATTEN. I think it is time we start compiling our own libraries of functions and gather them into one master template we can start from to build our models. Just image the sheer computational ease and power that would give us...
@@GeertDelmulle Yes, LAMBDA. I like it: FLATTEN : ) : )
Thanks a lot sir
You are welcome a lot : ) : )
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?
Thanks 😍🥰
You are welcome, Chi E : ) : )
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.
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.
If you have your named ranges you can, at least in office 365, just append the ranges by " =namedRange1:namedRange2:namedRange3 " etc. instead of using the choose-function.
This way you won't have the duplicates, but zero values on the 'missing' parts of the arrays.
Very Great Video but Hard for me.
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 :(
It might be from something like calling column 3, when no column 3 exists?
My Friend excelente video. How can i send you a direct mensage with my method to append list without tables and long formulas
Just post here. Two videos ago, we did Power Query to Append. Are you thinking about Power Query?
@@excelisfun nop. I use INDIRECT and FILTER formulas. Very simple method
@@hernan.cordero.24 Can you share your method please? We're all dying to know!
Sick. And you thought you were a power-user?
Riiiiiiiiight : ) : ) : ) : ) I agree on the "sick". Thanks to Bill Szysz : ) : ) : ) : )
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 🤦♂🤦♂🤦♂🤦♂
Oh, that didn't work? Maybe computers aren't your thing. Try a trade apprenticeship.
vstack can do this now
That is for sure!!! Can you believe how hard it was before VSTCK. I am soooooo glad Microsoft gave us H and V STCK : )
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.
A couple of years ago I made an entire simulation of the water level in a Waste Water Treatment tank and used Solve --> Genetic Algorithm to tune the pump firing sequence.
Changing the Waste Water Treatment Plant parameters and running the solver again would result in pump firing sequences for the new plant. But you could also hunt for it manually by looking at the graph and dashboard.
In Excel... No Macros...