Forum Discussion
Stacking The Beatles in Excel: An Exercise in 3D stacking
With this task, it seems it's inevitable CHOOSECOLS/CHOOSEROWS will be used. It's a matter of prepping the data to get to that point. The thunks certainly get the job done. I must say CHOOSECOLS/CHOOSEROWS seems to be very quick for all that's being asked (Much better than using INDEX).
This task would be easier with a little bit more flexibility!
The current list of functions that can handle a 3D reference is still limited even while adding in TOCOL/TOROW and TEXTJOIN to those listed in the link.
Create a 3-D reference to the same cell range on multiple worksheets (microsoft.com)
Patrick2788 Here is my approach. I did create 1 lambda to perform an iterative VStack operation:
=LET(in,PleasePleaseMe:LetItBe!$A$1:$F$31,
headers,LetItBe!$A$1:$F$1,
hCount,COLUMNS(headers),
hCountSeq,SEQUENCE(,hCount),
allcols,HSTACK(in),
allheaders, TAKE(allcols,1),
allHcount, COLUMNS(allheaders),
colIndex, hCount*INT(SEQUENCE(,allHcount,0,1/hCount))+MATCH(allheaders,headers,0),
cSorting,SORT(VSTACK(colIndex,allcols),1,1,1),
merge,VSTACK(headers,stackN(DROP(cSorting,2),hCount)),
FILTER(merge,CHOOSECOLS(merge,1)<>0))
so in line 11 is this stackN Lambda function:
LAMBDA(arr,n,
let(c,columns(arr),
if(c>n,
VSTACK(CHOOSECOLS(arr,sequence(,n)),stackn(CHOOSECOLS(arr,SEQUENCE(,columns(arr)-n,n+1)),n)),
arr)
))TBH I haven't studied your approach yet and curious to see how you solved it.
In my case I do use 2 inputs: the 3-d range and an input for the headers so you can pick which format you want, but that could be removed and automatically use the 1st page or something.
other than that I basically create an array "colindex" that give a 1,2,3... for the column headers based on the requested 'headers' order and then add an offset so it can be used to sort all the columns so all 'tabs' are in the same order. Then my stackN Lambda function does the Vstack.
This is a fascinating approach! I had some fun testing this solution. I added some dummy rows and extended your 3D reference to:
=PleasePleaseMe:LetItBe!$A$1:$F$60000It took a minute but Excel completed the calculation! I tried 65K and above but no dice - crash. Handling 720,000 rows is impressive.
Patrick2788 Here is a 3rd solution combining the above 2. So this recursive Lambda is real slick in that it can take any 2-d array with column headers (not necessarily sequential or grouped) and stack all the columns corresponding to the requested set of Headers for the output:
StackCommon = LAMBDA(arr, headers, IF( COLUMNS(headers) > 1, HSTACK( VSTACK( INDEX(headers, 1), TOCOL(FILTER(DROP(arr, 1), INDEX(arr, 1, ) = INDEX(headers, 1), ""),3,1) ), stackcommon(arr, DROP(headers, , 1)) ), VSTACK( headers, TOCOL(FILTER(DROP(arr, 1), INDEX(arr, 1, ) = headers, ""), 3, 1) ) ) )and calling formula is:
=LET(in,PleasePleaseMe:LetItBe!$A$1:$F$31, headers,LetItBe!$A$1:$F$1, StackCommon(HSTACK(in),headers) )- Since the solution is using recursion I wonder if more tabs would significantly diminish the performance (i.e. 120 tabs but 6000 rows).
I am still just learning these new text functions as you might notice I switched from CHOOSECOLS to DROP between the 2 LAMBDA functions and have to wonder if either is usable which is more or less efficient. For example:
CHOOSECOLS(arr, SEQUENCE(,n))
vs
DROP(arr, n-COLUMNS(arr))
mtarler OK so i looked at your solution and modified my recursive Lambda to work with it:
Stack =LAMBDA(arr, LET( c, COLUMNS(arr), uniqCols, COLUMNS(UNIQUE(chooserows(arr, 1), 1)), copies, c / uniqCols, IF( uniqCols > 1, hstack( vstack(INDEX(arr, 1, 1), tocol(drop(arr, 1, copies - c), 3, 1)), stack(drop(arr, , copies)) ), vstack(INDEX(arr, 1, 1), tocol(drop(arr, 1), 3, 1)) ) ) )and then called by:
=LET(in,PleasePleaseMe:LetItBe!$A$1:$F$31, Stack(SORT(HSTACK(in),1,1,1) ) )