Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 21, 2022
The more I try to approach this task from a fresh angle, the more I run into everyone else's solutions! I've played with sequencing and scanning but ultimately it goes back to solutions already posted.
Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 24, 2022

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))

 

Resources