Forum Discussion
Patrick2788
Sep 18, 2022Silver Contributor
Stacking The Beatles in Excel: An Exercise in 3D stacking
The setup: You're provided with an Excel workbook with 12 sheets for 12 Beatles albums. All 12 sheets have the same fields, but the fields are not in the same order in each sheet. The goal: Roll...
PeterBartholomew1
Sep 20, 2022Silver Contributor
I seem to be falling behind the curve here!
My formula using the thunk array was fine, but it was tedious forming the array in the first place. I also felt I was missing out on the new VSTACK 3D capability. I started the refactoring by defining thunks from the stacked array
LET(
a, SEQUENCE(12),
stacked, VSTACK(Albums3D),
tracksϑ, MAP(a, LAMBDA(a₁,
LAMBDA(TAKE(DROP(stacked,31*(a₁-1)),31)))),
// and so on
Then I realised that, since I know there are 31 rows set aside for the tracks within each album, I might as well use a Lambda function with the album number as a parameter. I therefore changed to
= LET(
stacked, VSTACK(Albums3D),
Hdrsλ, LAMBDA(a₁, TAKE(DROP(stacked,31*(a₁-1)),1)),
Tracksλ, LAMBDA(a₁, TAKE(DROP(stacked,1+31*(a₁-1)),30)),
//and then ...
a, SEQUENCE(,12),
expanded, REDUCE(Header, a,
LAMBDA(rollup,a₁,
LET(
hdr, Hdrsλ(a₁),
tracks, Tracksλ(a₁),
col, XMATCH(Header,hdr),
reordered, CHOOSECOLS(tracks, col),
VSTACK(rollup,reordered)
)
)
);
FILTER(expanded, ISTEXT(TAKE(expanded,,1)))
)
The question then is 'Why do my formulas continue to get larger whereas everyone else's gets shorter?'