Forum Discussion
Stacking The Beatles in Excel: An Exercise in 3D stacking
I have made some progress approaching the problem by building an array of thunks.
Worksheet formula
= REDUCE(Header, Albums,
LAMBDA(rollup,album,
LET(
hdr, TAKE(album(),1),
tracks, DROP(album(),1),
columns, XMATCH(Header, hdr),
reordered, CHOOSECOLS(tracks, columns),
VSTACK(rollup, reordered)
)
)
);
Albums
= VSTACK(
LAMBDA(PleasePleaseMe[#All]),
LAMBDA(WithTheBeatles[#All]),
LAMBDA(AHardDaysNight[#All]),
LAMBDA(BeatlesForSale[#All]),
LAMBDA(Help[#All])
);
The setup steps to form the array of thunks are somewhat tedious and error-prone even though the resulting worksheet formula is OK.
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.
