Forum Discussion
Return of the Corkscrew with Spilled Arrays
James_Buist Just for good measure, MMULT can also get the job done with minimal effort.
Closing Balance (cell G22):
=LET( data, HSTACK(F22:F24,G12:M14+G17:M19), cId, SEQUENCE(,COLUMNS(data)), arr, MMULT(--data,--(DROP(cId,,1)>=TOCOL(cId))), VSTACK(arr,BYCOL(arr,SUM)) )Cheers!
James_Buist Not too sure if this is what you're looking for, but the attached file contains a couple different possibilities using GROUPBY or SCAN...
Great. An entire collection. I used the first scan one. I have GroupBy but many don't yet have it but good to start getting some experience with it. The first Scan one is nice and simple to implement and doesn't require any named functions to be created. The second is probably more sophisticated but it can be easier for users not to have named functions unless they are quite simple. I find editing them still a bit of a pain even with the Excel labs editor. SO I tend to write functions using LET and then wrap in lambda and keep an original with indentation etc for easy reading. But there are probably better ways.
I was also not surprised when you added the MMULT. You are a master with those and they are so slick and fast. I've deployed both in the model and will decide later which to keep.
Many thanks again for the neat and succinct solution.PS I meant to add. I just used the Scan function for the Opening Balances as the closing could just be a sum of each part. I deliberately wanted to keep each section separate for added flexibility so I can model in multiple different ways but with the same component parts. Some corkscrews may have 5 or 6 middle sections so this way the function can just be tweaked to accommodate these especially if each block is just a single cell ref with a #.
