I've finally got LAMBDA!!!! I think this example probably doesn't utilise the best of Lambda and I can now evidence it! The thing about lambda is that it's recursive. You don't really want a function that returns three arrays, you want to pass a series of arrays to be recursively added to the list. That way if you do four or five or forty areas they simply use that recursive ability to add to the list. To do this I created two functions. One that combines two arrays (COMBINE2ARRAYS(array1,array2)) and one that calls that function recursively adding the cumulative area each time. Annoyingly you can't leave criteria Optional (or hidden to the user) in LAMBDA, so two of the outputs are essentially fixed in the recursive function. So we have: COMBINE2ARRAYS=LAMBDA(array1,array2,LET(array_1,array1,array_2,array2,r_1,ROWS(array_1),r_2,ROWS(array_2),c,SEQUENCE(,COLUMNS(array_1)),s,SEQUENCE(r_1+r_2),IF(s
Wow!!!! That is just what some of us were looking for! Some commenters asked about n arrays. Your recursive formula is great. I can't wait for a few hours to go by while you think up an even cooler version, Rico S : ) : ) Since I am not that smart, and it takes me a LONG time to learn new stuff, I will have to study your amazing function, or your coolier one when I get up tomorrow morning ; ) I AM JUST LOVING hanging out on our awesome online Excel Team. Go Team!!! Go Rico S : ) : ) : ) : ) One note: this function, I think, will only work on range references, since you are using the second option of INDEX, not with function generated arrays.
Mike told me to check this out. Super mega cool!!!!, recursive rocks!!!! Very clever !!!!✌✌Some suggestions though, if I might, you should amend the formulas to combine arrays of different column sizes, deal with errors that should be "" , and also , in an extraction or appending, very important to deal with blanks , they should not extract as 0's, they should stay null strings after results. The core is second syntax of INDEX with references and areas, and as I said before , when I used it too, has big disadvantage for 2 reasons, can append only arrays on the same spreadsheet, and can not be used inside formulas because of the bloody "reference" argument. Same like SUMIFS and COUNTIFS . Remember? 😀 Looking forward to the day when someone will be able to write a LAMBDA(ar1,ar2,ar3...arn) that can clean append n arrays spreaded anywhere on a workbook and that can also be called inside other formulas. Is there where we need them most, in constructing single cell formulas to compete with PQ and PT in general. That was the idea on the first place, to understand how appending works when we construct single cell formulas. Great work!!!!
@@Excelambda some great suggestions! There's no way to combine n arrays in Excel, you either come upon the limitations of number of parameters (253) or formula size (8192 chars). Thus the maximum you could combine would be 253. However, that's probably not a significant limiter, I'm guessing that you'll only ever use under 10, 99% of the time. The biggest problem is that you can't create optional parameters (I'll have to add a suggestion to the Excel folks, it should be possible to put parameter names in square brackets to make them optional), so you'd have to write CA(arr1,arr2,,,,,,,,,). The alternative is to create a single parameter with a defined splitter (~ for example) and use a split function to separate the arrays. The downside being that you can't then get the nice squares around your selected ranges. They are both reasonably straight forward, it's just the formula is a bit text heavy! You just create a function: COMBINEARRAYS(array1,array2..array43 etc). You pass array1 and array2 to the COMBINE2ARRAYS() function and then recursively call COMBINEARRAYS(combinedArray1Array2, array3..array42). You'd simply need to write it all out, and then add a catch to return array1 when you reach the first blank array. I think this is the best solution, but would need the optional parameter that Excel forgot to give us! That would be in line with other Excel functions such as let and sumifs that have optional criteria. Error handling is a must, and should take place in the lower function to save on formula text. To cope with multiple columns, you would first have to determine the maximum number of columns in the passed parameters. I would create a reusable function here: MAXCOLUMNS(array1, array2..array250), which calculates the max for the column sequence. That would also save on text, with the downside that you'd need to perform the calculation on each recurrence, or pass the maximum back as a parameter. It would be great if they allowed not only optional parameters, bit hidden parameters too. A hidden parameter could be a counter, or a cumulative array or a prior calculation that you want to step over in a loop. Anyway, I might try something better over the weekend!
@@ricos1497 Great points!!! "parameter names in square brackets to make them optional" Great Idea for Microsoft to chew.👌 When I said n I meant many , not infinite.😀 Managed to write a "string" formula with a single variable that writes a formula for appending n arrays that can be defined in other lambda and can be called in any other formulas that uses arrays from allover the workbook. Because of the string character limitations of 8192 chars it works for 93 arrays. So if you want a formula for appending 90 arrays I have it in 1sec .The reason that I was able to "automate" the process is because I found a formula that has patterns and does not use references or areas, uses the first syntax of INDEX, and patterns are good for recursive proceses. You are good with recursive and let's see if you can trick something. =LET(a1r,A2:E7,a2r,A9:D12,a3r,A15:E17,r1w,ROWS(a1r),r2w,ROWS(a2r),r3w,ROWS(a3r),c1l,COLUMNS(a1r),c2l,COLUMNS(a2r),c3l,COLUMNS(a3r),mr,MAX(r1w,r2w,r3w),tr,mr*3,tc,MAX(c1l,c2l,c3l),sqr,SEQUENCE(tr),sqc,SEQUENCE(,tc),qt,QUOTIENT(sqr-1,mr)+1,md,MOD(sqr-1,mr)+1,rs,IFERROR(IFS(sqr
Rico S, cr gr0912 made this formula for appending n tables in comments here: th-cam.com/video/XhcE9m18NbE/w-d-xo.html You two are so smart and amazing : ) : )
Hello, Mr. Girvin. Very nice approach. And you inspired me to develop a shorter Lamda/Let formula and, even I haven't tested it for more columns, it should work for any number of columns (given the 3 tables have the same number of columns, and in the same order). The only con is that it doesn't include the headers: =LAMBDA(Table1,Table2,Table3, LET(Idx, SEQUENCE(ROWS(Table1) + ROWS(Table2) + ROWS(Table3)), Jdx, TRANSPOSE(SEQUENCE(COLUMNS(Table1))), IFERROR(INDEX(Table1, Idx, Jdx), IFERROR(INDEX(Table2, Idx - ROWS(Table1), Jdx), INDEX(Table3, Idx - (ROWS(Table1) + ROWS(Table2)), Jdx))))) Tables 1, 2 and 3 are the three tables to append. Idx is the row index and Jdx is the column index (that's why its sequence is transposed). Contact me if you need my Excel file. Thanks for this great and inspiring video!
That is very cool, J. Obando!!!!! It is such a luxury to be on a Team with creative Excel people like you and so many of the others : ) : ) : ) I have added your solution to the download workbook. Thanks for contributing to the Team : )
Hi Mike. Have been tinkering with learning arrays in VBA. Came up with the following UDF that appends three tables just like your formulas. If in MS365, it spills like a dynamic array function from one cell entry. If not in MS365, need to highlight the entire output range first. Sadly, VBA gets no more love from Microsoft, but you can still solve a lot of problems with it. Posting the code, just in case anyone is interested. Thanks and Thumbs up!! Function AppendTables(rng1 As Range, rng2 As Range, rng3 As Range) Dim r1 As Long, r2 As Long, r3 As Long, c As Long r1 = rng1.Rows.Count r2 = rng2.Rows.Count r3 = rng3.Rows.Count c = rng1.Columns.Count Dim arr As Variant ReDim arr(1 To r1 + r2 + r3, 1 To c) Dim i As Long, j As Long For i = 1 To r1 + r2 + r3 For j = 1 To c If i
Wow, really Amazing explanation. With all that graphics, wow. Fantastic . I am looking at it as to a Marvel movie. Visualizing effects are absolutely awesome!!!!!. I have to say that could be a shorter formula using FILTERXML, but I leave this to ExcelWizard. It's a good practice to understand how excels works, very useful for creating single cell formulas appending arrays .😀✌✌✌
Thank you for thr tricks, cr gr0912 : ) : ) : ) : ) : ) Excel Wizard and David have a great FILTERXML append trick : ) What a great Team we have! Anand post about Davids video below: "Mr. Abiola David has done using filterxml function at this link> th-cam.com/video/50x6SCH2OFI/w-d-xo.html " Go Team!!!!!
@@excelisfun David's FILTERXML very cool indeed. Very important , there is another approach for appending arrays, a formula that uses the second INDEX syntax the one with references and area numbers =LET(a1r,B6:C9,a2r,E6:F10,a3r,H6:I15,r1w,ROWS(a1r),r2w,ROWS(a2r),r3w,ROWS(a3r),c1m,COLUMNS(a1r),c2m,COLUMNS(a2r),c3m,COLUMNS(a3r),mr,MAX(r1w,r2w,r3w),r,mr*3,c,MAX(c1m,c2m,c3m),sqr,SEQUENCE(r),sqc,SEQUENCE(,c),qt,QUOTIENT(sqr-1,mr)+1,md,MOD(sqr-1,mr)+1,ar,INDEX((a1r,a2r,a3r),md,sqc,qt),arr,IFERROR(ar,""),fc,INDEX(arr,,1),arf,FILTER(arr,fc""),arf) This formula is writen for different columns dimensions arrays. Check it out appending arrays with dif nr of columns!! Though, it has a Huge drawback, uses references , not arrays. Is ok when you have the arrays explicit on the spreadsheet, but when we want to append arrays that are calculated with other arrays operations in complex formulas, do not work (exactly like sumifs and countifs). Thats why your example is more useful in real life for complex formulas.
cr gr0912 , ExcelIsFun. Thanks for the mention. 😊 =INDEX(FILTERXML(""&TEXTJOIN("",,"Names","Grade",B6:C9,E6:F10,H6:I15)&"","//m"),SEQUENCE(COUNT(B6:C9,E6:F10,H6:I15)+1,2)) I also have a clip in Thai th-cam.com/video/0mxhftN4Fk4/w-d-xo.html
I like the part that says, when you get a new data, =Append3GradeTable{Table 1,Table 2, Table 3}😂😂😂, but the formula construction is a mind blower, but all in all, the intro is very great, Great work from geert 👍👍👍
Awesome formula and new intro too! Starting off 2021 with a bang. Thanks for the amazing tricks to stack three tables all with a single formula :)) Thumbs up!!
Bloody brilliant. Happy New Year. LAMBDA, LET, and the other dynamic array formulas are great. I can’t wait for that functionality to be added to all the Excel functions. Things that were impossible or extremely complex are now possible or a lot easier
You are soooo right: " Things that were impossible or extremely complex are now possible or a lot easier"!!!!!!!!!! I can't wait for you to get them too : )
@@excelisfun I’ve actually had them for a while. When I started getting really interested in Excel over the last 2 years, I quickly signed up for 365 and the beta channel. Through you, Mr. Excel, and a few others, I have learned so much. Microsoft is changing how people view and use Excel. I have a little bit of a programming background being a mechatronics engineer. At my last job, I was using Excel for reports in the gas industry about regulators. I had a lot of behind the scenes stuff that was almost always the same. These new functions made it so much better
@@patrickschardt7724 Cool: mechatronics engineer : ) I agree with you and this is such a good thing: "Microsoft is changing how people view and use Excel. " Excel is the most used program in the world and more than 1/2 of all business decisions in the world are based on Excel output, so it is great the Microsoft is making Excel better and more legitimate.
😍👍 Wow, Fantastic explanation and the graphics. Another Let +INDEX append =LET(a,B6:C9,b,E6:F10,c,H6:I15,s,SEQUENCE(COUNT(a,b,c)+1,,-1),l,CHOOSE({1;2;3},0,ROWS(a),ROWS(a)+ROWS(b)),IFNA(INDEX((a,b,c),s-LOOKUP(s,l)+1,{1,2},MATCH(s,l)),{"Names","Grades"}))
For more flexible, use ROWS(a)+ROWS(b)+ROWS(c) instead of COUNT(a,b,c) =LET(a,B6:C9,b,E6:F10,c,H6:I15,s,SEQUENCE(ROWS(a)+ROWS(b)+ROWS(c)+1,,-1),l,CHOOSE({1;2;3},0,ROWS(a),ROWS(a)+ROWS(b)),IFNA(INDEX((a,b,c),s-LOOKUP(s,l)+1,{1,2},MATCH(s,l)),{"Names","Grades"}))
Another lambda for append 2 tables but use twice for append 3 tables Append2 =LAMBDA(a,b,LET(s,SEQUENCE(COUNTA(INDEX(a,,1),INDEX(b,,1))),l,ROWS(a)*{0;1},CHOOSE(MATCH(s,l),INDEX(a,s,SEQUENCE(,COLUMNS(a))),INDEX(b,s-ROWS(a),SEQUENCE(,COLUMNS(a)))))) then =Append2(Append2(B6:C9,E6:F10),H6:I15)
If you need to every do this with a worksheet formula, it is good to know this great method. We are lucky to be on such a great Team with so many smart people : )
hello, can you help? i wish to spill an array, but I also want to add Header rows to spilled calculation. Like you did here, but I don't need to append tables. I just need somehow to get headers rows and then filter my table.
This vedio is on 5 jan 2021 but vstack released in march 2022 I think we can use vstack that is much more easier than this complicated let and lambda function =VSTACK({"Name","Grade"},B6:C9,E6:F10,H6:I15)
Hey Mike! Thanks a lot! always amazing to watch your videos. Is there a way to vary the number of tables that we can append? As I understood your formula works only with 3 tables.
Now, we finally have a way of stacking arrays (albeit a little convoluted - MS should make an easy to use function for this). BTW: thanks for all that exposure, Mike! Hopefully, I don’t have to run and hide from a bunch of crazy fans, now. LOL! :-)
@@excelisfun Mike, that is always possible. But there are consequences: impact on audio, but to name one. The length is exactly 20 seconds. How long should it be? Maybe the length of the music of your previous intro? That piece of music is also a lot more upbeat than what comes with the video, and it fits your style better, too. But mind you: it should definitely NOT be just a linear clip speed change, lest we loose the effect of the intro. BTW: one of the reasons that it doesn’t work as an outtro is: the last frame of the video.
@@excelisfun Thanks Mike. I'll hopefully have some time this weekend to try out some ideas. One question I have though. Can you or anyone else on here think of an easy way to make Lambda functions reusable across several workbooks whilst keeping them all in one central depository or library? I imagine I could just keep the code 'text' along with the User Defined Function (UDF) name in a separate workbook or even Notepad file, but I was hoping for a more elegant solution. I've Googled this to bits, but the terminology is all too generic to give me any relevant solutions. Regards,
@@alanmonaghan9194 I think you can save the Defined name in the default workbook that opens. I have not done it in a while, so I would have to lookup how to do it - but I think it is just save the file in the start folder...
I like this idea but for me it is nicer to use a UDF instead. Hopefully MS comes up with a similar matrix function Function append2Ranges(range1 As Range, range2 As Range) As Variant Dim ra1 As Variant Dim ra2 As Variant Dim raAppend As Variant Dim new_size As Long Dim e As Variant Dim row As Long: row = 0 Dim col As Integer: col = 0 Dim cols As Integer
ra1 = range1.Value ra2 = range2.Value 'check if same number of columns If UBound(ra1, 2) UBound(ra2, 2) Then append2Ranges = CVErr(xlErrNA) Exit Function Else cols = UBound(ra1, 2) - 1 End If
'1st data from range For row = 0 To UBound(ra1, 1) - 1 For col = 0 To cols raAppend(row, col) = ra1(row + 1, col + 1) Next Next '2nd data from range For row = row To row + UBound(ra2, 1) - 1 For col = 0 To cols raAppend(row, col) = ra2(row - UBound(ra1, 1) + 1, col + 1) Next Next append2Ranges = raAppend End Function
ALTERNATIVE 'LET' FORMULA (35% SHORTER) : =LET(title,"Names","Grade"},Array1,B6:C9,Array2,E6:F10,Array3,H6:I15,s,SEQUENCE(ROWS(Array1)+ROWS(Array2)+ROWS(Array3)+ROWS(title)), IFS(s=1,title, s
I've finally got LAMBDA!!!!
I think this example probably doesn't utilise the best of Lambda and I can now evidence it! The thing about lambda is that it's recursive. You don't really want a function that returns three arrays, you want to pass a series of arrays to be recursively added to the list. That way if you do four or five or forty areas they simply use that recursive ability to add to the list.
To do this I created two functions. One that combines two arrays (COMBINE2ARRAYS(array1,array2)) and one that calls that function recursively adding the cumulative area each time. Annoyingly you can't leave criteria Optional (or hidden to the user) in LAMBDA, so two of the outputs are essentially fixed in the recursive function. So we have:
COMBINE2ARRAYS=LAMBDA(array1,array2,LET(array_1,array1,array_2,array2,r_1,ROWS(array_1),r_2,ROWS(array_2),c,SEQUENCE(,COLUMNS(array_1)),s,SEQUENCE(r_1+r_2),IF(s
Wow!!!! That is just what some of us were looking for! Some commenters asked about n arrays. Your recursive formula is great. I can't wait for a few hours to go by while you think up an even cooler version, Rico S : ) : ) Since I am not that smart, and it takes me a LONG time to learn new stuff, I will have to study your amazing function, or your coolier one when I get up tomorrow morning ; ) I AM JUST LOVING hanging out on our awesome online Excel Team. Go Team!!! Go Rico S : ) : ) : ) : )
One note: this function, I think, will only work on range references, since you are using the second option of INDEX, not with function generated arrays.
Mike told me to check this out. Super mega cool!!!!, recursive rocks!!!! Very clever !!!!✌✌Some suggestions though, if I might, you should amend the formulas to combine arrays of different column sizes, deal with errors that should be "" , and also , in an extraction or appending, very important to deal with blanks , they should not extract as 0's, they should stay null strings after results. The core is second syntax of INDEX with references and areas, and as I said before , when I used it too, has big disadvantage for 2 reasons, can append only arrays on the same spreadsheet, and can not be used inside formulas because of the bloody "reference" argument. Same like SUMIFS and COUNTIFS . Remember? 😀 Looking forward to the day when someone will be able to write a LAMBDA(ar1,ar2,ar3...arn) that can clean append n arrays spreaded anywhere on a workbook and that can also be called inside other formulas. Is there where we need them most, in constructing single cell formulas to compete with PQ and PT in general. That was the idea on the first place, to understand how appending works when we construct single cell formulas. Great work!!!!
@@Excelambda some great suggestions!
There's no way to combine n arrays in Excel, you either come upon the limitations of number of parameters (253) or formula size (8192 chars). Thus the maximum you could combine would be 253. However, that's probably not a significant limiter, I'm guessing that you'll only ever use under 10, 99% of the time. The biggest problem is that you can't create optional parameters (I'll have to add a suggestion to the Excel folks, it should be possible to put parameter names in square brackets to make them optional), so you'd have to write CA(arr1,arr2,,,,,,,,,). The alternative is to create a single parameter with a defined splitter (~ for example) and use a split function to separate the arrays. The downside being that you can't then get the nice squares around your selected ranges. They are both reasonably straight forward, it's just the formula is a bit text heavy! You just create a function: COMBINEARRAYS(array1,array2..array43 etc). You pass array1 and array2 to the COMBINE2ARRAYS() function and then recursively call COMBINEARRAYS(combinedArray1Array2, array3..array42). You'd simply need to write it all out, and then add a catch to return array1 when you reach the first blank array. I think this is the best solution, but would need the optional parameter that Excel forgot to give us! That would be in line with other Excel functions such as let and sumifs that have optional criteria. Error handling is a must, and should take place in the lower function to save on formula text. To cope with multiple columns, you would first have to determine the maximum number of columns in the passed parameters. I would create a reusable function here: MAXCOLUMNS(array1, array2..array250), which calculates the max for the column sequence. That would also save on text, with the downside that you'd need to perform the calculation on each recurrence, or pass the maximum back as a parameter. It would be great if they allowed not only optional parameters, bit hidden parameters too. A hidden parameter could be a counter, or a cumulative array or a prior calculation that you want to step over in a loop.
Anyway, I might try something better over the weekend!
@@ricos1497 Great points!!! "parameter names in square brackets to make them optional" Great Idea for Microsoft to chew.👌 When I said n I meant many , not infinite.😀 Managed to write a "string" formula with a single variable that writes a formula for appending n arrays that can be defined in other lambda and can be called in any other formulas that uses arrays from allover the workbook. Because of the string character limitations of 8192 chars it works for 93 arrays. So if you want a formula for appending 90 arrays I have it in 1sec .The reason that I was able to "automate" the process is because I found a formula that has patterns and does not use references or areas, uses the first syntax of INDEX, and patterns are good for recursive proceses. You are good with recursive and let's see if you can trick something.
=LET(a1r,A2:E7,a2r,A9:D12,a3r,A15:E17,r1w,ROWS(a1r),r2w,ROWS(a2r),r3w,ROWS(a3r),c1l,COLUMNS(a1r),c2l,COLUMNS(a2r),c3l,COLUMNS(a3r),mr,MAX(r1w,r2w,r3w),tr,mr*3,tc,MAX(c1l,c2l,c3l),sqr,SEQUENCE(tr),sqc,SEQUENCE(,tc),qt,QUOTIENT(sqr-1,mr)+1,md,MOD(sqr-1,mr)+1,rs,IFERROR(IFS(sqr
Rico S, cr gr0912 made this formula for appending n tables in comments here: th-cam.com/video/XhcE9m18NbE/w-d-xo.html You two are so smart and amazing : ) : )
Hello, Mr. Girvin. Very nice approach. And you inspired me to develop a shorter Lamda/Let formula and, even I haven't tested it for more columns, it should work for any number of columns (given the 3 tables have the same number of columns, and in the same order). The only con is that it doesn't include the headers:
=LAMBDA(Table1,Table2,Table3, LET(Idx, SEQUENCE(ROWS(Table1) + ROWS(Table2) + ROWS(Table3)), Jdx, TRANSPOSE(SEQUENCE(COLUMNS(Table1))), IFERROR(INDEX(Table1, Idx, Jdx), IFERROR(INDEX(Table2, Idx - ROWS(Table1), Jdx), INDEX(Table3, Idx - (ROWS(Table1) + ROWS(Table2)), Jdx)))))
Tables 1, 2 and 3 are the three tables to append. Idx is the row index and Jdx is the column index (that's why its sequence is transposed). Contact me if you need my Excel file.
Thanks for this great and inspiring video!
That is very cool, J. Obando!!!!! It is such a luxury to be on a Team with creative Excel people like you and so many of the others : ) : ) : )
I have added your solution to the download workbook. Thanks for contributing to the Team : )
@@excelisfun It's an honor to contribute in such an AAA+ Team. I have learned so much in these 12-13 years, from you and the Team. Thanks!
@@JorgeObando Go Team!!!!!!
Thanks Mike, you became one of my all time favorite teacher
Glad to help with the teaching on such a great Team with teammates like Geert and cr gr0912 : )
Hi Mike. Have been tinkering with learning arrays in VBA. Came up with the following UDF that appends three tables just like your formulas. If in MS365, it spills like a dynamic array function from one cell entry. If not in MS365, need to highlight the entire output range first. Sadly, VBA gets no more love from Microsoft, but you can still solve a lot of problems with it. Posting the code, just in case anyone is interested. Thanks and Thumbs up!!
Function AppendTables(rng1 As Range, rng2 As Range, rng3 As Range)
Dim r1 As Long, r2 As Long, r3 As Long, c As Long
r1 = rng1.Rows.Count
r2 = rng2.Rows.Count
r3 = rng3.Rows.Count
c = rng1.Columns.Count
Dim arr As Variant
ReDim arr(1 To r1 + r2 + r3, 1 To c)
Dim i As Long, j As Long
For i = 1 To r1 + r2 + r3
For j = 1 To c
If i
Thanks for posting this awesome VBA solution, Wayne. Thanks for greatly contributing to the Team : ) : ) : ) : )
Wow, really Amazing explanation. With all that graphics, wow. Fantastic . I am looking at it as to a Marvel movie. Visualizing effects are absolutely awesome!!!!!.
I have to say that could be a shorter formula using FILTERXML, but I leave this to ExcelWizard. It's a good practice to understand how excels works, very useful for creating single cell formulas appending arrays .😀✌✌✌
Thank you for thr tricks, cr gr0912 : ) : ) : ) : ) : )
Excel Wizard and David have a great FILTERXML append trick : ) What a great Team we have! Anand post about Davids video below: "Mr. Abiola David has done using filterxml function at this link> th-cam.com/video/50x6SCH2OFI/w-d-xo.html "
Go Team!!!!!
@@excelisfun David's FILTERXML very cool indeed.
Very important , there is another approach for appending arrays, a formula that uses the second INDEX syntax the one with references and area numbers
=LET(a1r,B6:C9,a2r,E6:F10,a3r,H6:I15,r1w,ROWS(a1r),r2w,ROWS(a2r),r3w,ROWS(a3r),c1m,COLUMNS(a1r),c2m,COLUMNS(a2r),c3m,COLUMNS(a3r),mr,MAX(r1w,r2w,r3w),r,mr*3,c,MAX(c1m,c2m,c3m),sqr,SEQUENCE(r),sqc,SEQUENCE(,c),qt,QUOTIENT(sqr-1,mr)+1,md,MOD(sqr-1,mr)+1,ar,INDEX((a1r,a2r,a3r),md,sqc,qt),arr,IFERROR(ar,""),fc,INDEX(arr,,1),arf,FILTER(arr,fc""),arf)
This formula is writen for different columns dimensions arrays. Check it out appending arrays with dif nr of columns!!
Though, it has a Huge drawback, uses references , not arrays. Is ok when you have the arrays explicit on the spreadsheet, but when we want to append arrays that are calculated with other arrays operations in complex formulas, do not work (exactly like sumifs and countifs).
Thats why your example is more useful in real life for complex formulas.
cr gr0912
,
ExcelIsFun. Thanks for the mention. 😊
=INDEX(FILTERXML(""&TEXTJOIN("",,"Names","Grade",B6:C9,E6:F10,H6:I15)&"","//m"),SEQUENCE(COUNT(B6:C9,E6:F10,H6:I15)+1,2))
I also have a clip in Thai
th-cam.com/video/0mxhftN4Fk4/w-d-xo.html
@@Excelambda Very cool area reference trick, cr gr0912!!!!
@@ExcelWizard I LOVE it!!!!! Can I make a video of your (and David's) trick?
I like the part that says, when you get a new data, =Append3GradeTable{Table 1,Table 2, Table 3}😂😂😂, but the formula construction is a mind blower, but all in all, the intro is very great, Great work from geert 👍👍👍
What a great Team we have!!!!!! Go Team!!!!!
@@excelisfun Again and again, extraordinary things always come from the team, great team indeed 🙏🙏🙏🙏
@@ogwalfrancis This is true: extraordinary things always come from the team, great team indeed
@@excelisfun Thanks for the efforts
@@ogwalfrancis : ) : )
Awesome formula and new intro too! Starting off 2021 with a bang. Thanks for the amazing tricks to stack three tables all with a single formula :)) Thumbs up!!
Yes start out with a bang with our awesome Team with many such as Geert and cr gr0912 : )
Bloody brilliant. Happy New Year. LAMBDA, LET, and the other dynamic array formulas are great. I can’t wait for that functionality to be added to all the Excel functions. Things that were impossible or extremely complex are now possible or a lot easier
You are soooo right: " Things that were impossible or extremely complex are now possible or a lot easier"!!!!!!!!!! I can't wait for you to get them too : )
@@excelisfun I’ve actually had them for a while. When I started getting really interested in Excel over the last 2 years, I quickly signed up for 365 and the beta channel. Through you, Mr. Excel, and a few others, I have learned so much. Microsoft is changing how people view and use Excel. I have a little bit of a programming background being a mechatronics engineer. At my last job, I was using Excel for reports in the gas industry about regulators. I had a lot of behind the scenes stuff that was almost always the same. These new functions made it so much better
@@patrickschardt7724 Cool: mechatronics engineer : ) I agree with you and this is such a good thing: "Microsoft is changing how people view and use Excel. " Excel is the most used program in the world and more than 1/2 of all business decisions in the world are based on Excel output, so it is great the Microsoft is making Excel better and more legitimate.
Amazing formula and glad to hear there is a screen tip for Lambda.
Screen tip for introduction to function. Still no argument screen tip.. : (
Great intro!
Yes, we must thank Geert, The Exceleer!!!!
He did a really great job!
Awesome.
Glad it is awesome! Lucky, cr gr0912 commented about thes tricks : )
Thanks for this great job. Impressive the power of the LAMBDA function (very important to add the comments). The year starts with great force!
Ye!!! I love this: The year starts with great force!
Boom!Amazing Formulas To Start 2021!!Definietley Blown The Holiday Cobwebs Away...Thank You Mike :)
Those holiday cobwebs don't stand a BOOM chance!!!!!
Incredible, Mike!
Glad it ya like it, renegadek!!! Lucky we have a great Team!!!
Very Interesting starting video Animation of Your channel + very very Interesting video.
Thanks, SIMFINSO!!!!
Lambda - I've just checked and finnaly got it. Awesome video, as always :)
Awesome!!! Glad you got it - it is so wonderful : )
You left me with an open mouth!!!! I will definitely download. This is good practice. :) :)
Good pratcie. The FILTERXML formula from David and Excel Wizard is too good too : ) : )
You know it's gonna be a mind-bending video when a title is sth related to LET and Dynamic array and the video is 15 mins long!!!!
So TRUE : ) : )
😍👍 Wow, Fantastic explanation and the graphics.
Another Let +INDEX append
=LET(a,B6:C9,b,E6:F10,c,H6:I15,s,SEQUENCE(COUNT(a,b,c)+1,,-1),l,CHOOSE({1;2;3},0,ROWS(a),ROWS(a)+ROWS(b)),IFNA(INDEX((a,b,c),s-LOOKUP(s,l)+1,{1,2},MATCH(s,l)),{"Names","Grades"}))
For more flexible, use ROWS(a)+ROWS(b)+ROWS(c) instead of COUNT(a,b,c)
=LET(a,B6:C9,b,E6:F10,c,H6:I15,s,SEQUENCE(ROWS(a)+ROWS(b)+ROWS(c)+1,,-1),l,CHOOSE({1;2;3},0,ROWS(a),ROWS(a)+ROWS(b)),IFNA(INDEX((a,b,c),s-LOOKUP(s,l)+1,{1,2},MATCH(s,l)),{"Names","Grades"}))
Both are beautiful, Excel Wizard!!!!! Thanks for the cool Teammate formulas : ) I have added them to the download workbook.
@@excelisfun Thanks, go Team!😊
Another lambda for append 2 tables but use twice for append 3 tables
Append2
=LAMBDA(a,b,LET(s,SEQUENCE(COUNTA(INDEX(a,,1),INDEX(b,,1))),l,ROWS(a)*{0;1},CHOOSE(MATCH(s,l),INDEX(a,s,SEQUENCE(,COLUMNS(a))),INDEX(b,s-ROWS(a),SEQUENCE(,COLUMNS(a))))))
then
=Append2(Append2(B6:C9,E6:F10),H6:I15)
@@ExcelWizard Way cool!!! I added it to the download for our Team : )
Awesome tricks!
Thanks, Teammate!
Awesome Man...
Appreciate👏👏👌👌
Glad it is awesome! We have a great Team!!!
Amazing!!! 👍 Thank you!
Glad it is amazing for you, Davor : ) We have a great Team!!!
Wow, It was quite a masterstroke. Thank You Mike :)
If you need to every do this with a worksheet formula, it is good to know this great method. We are lucky to be on such a great Team with so many smart people : )
Thank you so much for this amazing video! This was awesome!
You are welcome, Eric!!! We have a great Team : )
hello, can you help?
i wish to spill an array, but I also want to add Header rows to spilled calculation. Like you did here, but I don't need to append tables.
I just need somehow to get headers rows and then filter my table.
Absolutely awesome!
Yes, but MUCH more complicated that your formula here : th-cam.com/video/50x6SCH2OFI/w-d-xo.html
: ) : ) : ) : ) : ) : )
Thanks for the smartness : )
Excel really is powerful!
It sure is : ) : )
This vedio is on 5 jan 2021 but vstack released in march 2022
I think we can use vstack that is much more easier than this complicated let and lambda function
=VSTACK({"Name","Grade"},B6:C9,E6:F10,H6:I15)
Yes, yes, yes!!!!!
That's Amazing ... thanks Mike
You aer welcome!!! We have a great Team : ) : ) Thanks to Geert and cr gr0912 : )
Fantastic!!!
Glad you liked it, bevon!!!
Thanks, I really enjoyed
You are welcome! It is lucky we have such a great Team!!!!
Hey Mike! Thanks a lot! always amazing to watch your videos. Is there a way to vary the number of tables that we can append? As I understood your formula works only with 3 tables.
Yes, it is only for three. I have not developed a variable one. I am sure it can be done, though.
This is Great (again!!)
Can‘t wait to get the LAMBDA-function. Actually it is not availible at my BetaChannel-machines.
Microsoft told me (email) that all beta users will now be updated to include Lambda - and indeed I got it today
@@henryg5735 YEAH! Got it today, too!
Glad you both got it!!!!
@@henryg5735 Me, too. Today. Yay! :-)
Now, we finally have a way of stacking arrays (albeit a little convoluted - MS should make an easy to use function for this).
BTW: thanks for all that exposure, Mike!
Hopefully, I don’t have to run and hide from a bunch of crazy fans, now. LOL! :-)
PS: I’m gonna look for another outtro - I just don’t feel that the intro works as well in that position...
Give me some time.
PS2: and I’ve finally been able to give something back to you. :-)
@@GeertDelmulle I think the intro has to be shorter. What do you think about that?
I am leading the pack of fans running to get your autograph : ) : ) : ) : ) : ) : )
@@excelisfun Mike, that is always possible. But there are consequences: impact on audio, but to name one.
The length is exactly 20 seconds. How long should it be? Maybe the length of the music of your previous intro?
That piece of music is also a lot more upbeat than what comes with the video, and it fits your style better, too.
But mind you: it should definitely NOT be just a linear clip speed change, lest we loose the effect of the intro.
BTW: one of the reasons that it doesn’t work as an outtro is: the last frame of the video.
Hello Mike, how can let know whether formula is not a array formula given there is no curly braces 🤔
I think it’s too complicated for such common operation. Why no Append function from Microsoft?
I agree. I hope they can create an Append formula soon : ) : )
I'm on the Beta channel but only got the LAMBDA function today a month later than other Beta people. I must be a Gamma tester! LOL
I just got it today too in sunny Scotland.
lol Gamma testers are cool ; )
@@alanmonaghan9194 Cool, glad you got it : )
@@excelisfun Thanks Mike. I'll hopefully have some time this weekend to try out some ideas.
One question I have though. Can you or anyone else on here think of an easy way to make Lambda functions reusable across several workbooks whilst keeping them all in one central depository or library? I imagine I could just keep the code 'text' along with the User Defined Function (UDF) name in a separate workbook or even Notepad file, but I was hoping for a more elegant solution. I've Googled this to bits, but the terminology is all too generic to give me any relevant solutions.
Regards,
@@alanmonaghan9194 I think you can save the Defined name in the default workbook that opens. I have not done it in a while, so I would have to lookup how to do it - but I think it is just save the file in the start folder...
I like this idea but for me it is nicer to use a UDF instead. Hopefully MS comes up with a similar matrix function
Function append2Ranges(range1 As Range, range2 As Range) As Variant
Dim ra1 As Variant
Dim ra2 As Variant
Dim raAppend As Variant
Dim new_size As Long
Dim e As Variant
Dim row As Long: row = 0
Dim col As Integer: col = 0
Dim cols As Integer
ra1 = range1.Value
ra2 = range2.Value
'check if same number of columns
If UBound(ra1, 2) UBound(ra2, 2) Then
append2Ranges = CVErr(xlErrNA)
Exit Function
Else
cols = UBound(ra1, 2) - 1
End If
new_size = UBound(ra1, 1) + UBound(ra2, 1) - 1
ReDim raAppend(new_size, UBound(ra1, 2) - 1)
'1st data from range
For row = 0 To UBound(ra1, 1) - 1
For col = 0 To cols
raAppend(row, col) = ra1(row + 1, col + 1)
Next
Next
'2nd data from range
For row = row To row + UBound(ra2, 1) - 1
For col = 0 To cols
raAppend(row, col) = ra2(row - UBound(ra1, 1) + 1, col + 1)
Next
Next
append2Ranges = raAppend
End Function
ALTERNATIVE 'LET' FORMULA (35% SHORTER) :
=LET(title,"Names","Grade"},Array1,B6:C9,Array2,E6:F10,Array3,H6:I15,s,SEQUENCE(ROWS(Array1)+ROWS(Array2)+ROWS(Array3)+ROWS(title)),
IFS(s=1,title,
s
Now just vstack
What is#
# is spilled range operator. With spilled arrays, the formula only lives in the top cell. So to get spilled results from that formula, you use #
th-cam.com/video/6LdKqHQre9g/w-d-xo.html
Laughs in Python