Stacking The Beatles in Excel: An Exercise in 3D stacking

Well done!

I've been doing a lot of table transformations with INDEX-SEQUENCE algorithms recently, so I took a crack at it:

=LAMBDA(ref_3d,
   LET(
      a, HSTACK(ref_3d),
      b, TAKE(a,1),
      h, ROWS(a)-1,
      w, COLUMNS(a),
      m, SHEETS(ref_3d),
      n, w/m,
      k, SEQUENCE(h*m,,0),
      x, DROP(SORTBY(a,QUOTIENT(SEQUENCE(,w,0),n),1,b,1),1),
      y, INDEX(x,MOD(k,h)+1,QUOTIENT(k,h)*n+SEQUENCE(,n)),
      VSTACK(UNIQUE(SORT(b,,,1),1),FILTER(y,TAKE(y,,1)<>""))
   )
)

It's a little longer, but should hold up well performance-wise.

If any one of the worksheets were missing a field, though, I think an unpivot-repivot method might be the way to go. Something like:

=LET(
   a, HSTACK(Albums3D),
   b, DROP(a,1),
   w, COLUMNS(a),
   i, SEQUENCE(ROWS(b)),
   j, QUOTIENT(SEQUENCE(,w,0),w/SHEETS(Albums3D)),
   t, b<>"",
   f, LAMBDA(x,TOCOL(IFS(t,x),2)),
   DROP(PIVOTBY(HSTACK(f(j),f(i)),f(TAKE(a,1)),f(b),SINGLE,0,0,,0),,2)
)

Or:

=LET(
   a, HSTACK(Albums3D),
   b, DROP(a,1),
   v, TOCOL(b),
   w, COLUMNS(a),
   i, SEQUENCE(ROWS(b)),
   j, QUOTIENT(SEQUENCE(,w,0),w/SHEETS(Albums3D)),
   f, LAMBDA(x,TOCOL(IF({1},x,b))),
   DROP(PIVOTBY(HSTACK(f(j),f(i)),f(TAKE(a,1)),v,SINGLE,0,0,,0,,v<>""),,2)
)

I'm not really sure if there's any difference in efficiency between the two. I'd like to think the second one is faster because it removes the blank records in one swoop with the [filter_array] argument of PIVOTBY, whereas the first method removes the blank records for every instance of TOCOL (4 times), but I didn't run any tests with larger datasets.

Cheers!

I can't even open the file without crashing Excel!

I had better check whether it is Excel or the files.

Patrick2788 

This is my attempt to approach this from a different angle. Although, you'll see everyone's solutions have inspired me in a way.

The 3D reference. Same as 1st attempt:

=PleasePleaseMe:LetItBe!$A$1:$F$31

The header pulled from the Let it Be sheet because that's the column order I'm going for:

=LetItBe!$A$1:$F$1

The 3D stack. This time with VSTACK:

=VSTACK(Albums3D)

This is where my solution is different.  This formula pulls the header from each album and repeats it 31 times.  I've added SHEETS() so Magical Mystery Tour can be added!

=CHOOSEROWS(SEQUENCE(SHEETS()-1,,1,31),INT(SEQUENCE((SHEETS()-1)*31,,1,1/31)))

This formula checks the column position by each row and strings together an array:

=LET(c,CHOOSEROWS(VSTACK(Albums3D),SeqRows),BYROW(c,LAMBDA(row,TEXTJOIN(",",,XMATCH(header,row)))))

Which looks like this:

Next, the above array is reduced to a scalar, split, and wrapped:

=WRAPROWS(TEXTSPLIT(TEXTJOIN(",",,strArr),","),6)

 Which looks like this:

Lastly, the above array is fed to INDEX, some filtering is done, and the header is added:

=LET(tracks,INDEX(StackedAlbums,SEQUENCE((SHEETS()-1)*31),arr),f,FILTER(tracks,(TAKE(tracks,,-1)>0)*(ISNUMBER(TAKE(tracks,,-1)))),VSTACK(header,f))

SergeiBaklan  inspired me to step it up to be even more simple:

=LET(in,Albums3D,
headers,LetItBe!$A$1:$F$1,
arr,HSTACK(in),
VSTACK(headers,DROP(REDUCE("",headers,LAMBDA(a,i,
    HSTACK(a,TOCOL(FILTER(DROP(arr,1),INDEX(arr,1,)=i,""),3,1)))),,1))
)

PeterBartholomew1 

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)