Forum Discussion
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...
More straightforward
=LET(
header, {"Album","Length","No.","Side","Title","Vocals"},
stack, VSTACK(Albums3D),
k, SEQUENCE( ROWS( stack ) ),
titleRows, SCAN(1, k, LAMBDA(a,v,
IF( AND( ISNA( XMATCH( CHOOSEROWS(stack,v),header) ) ), a, v ) ) ),
raw, DROP( REDUCE("", k, LAMBDA(a,v,
LET( order, XMATCH( header, CHOOSEROWS( stack, CHOOSEROWS(titleRows,v) ) ),
VSTACK( a, CHOOSECOLS( CHOOSEROWS( stack, v), order ) ) )
) ), 1 ),
return, FILTER( raw, ( CHOOSECOLS(raw,1) <> 0 ) *
( CHOOSECOLS( raw,1 ) <> INDEX( header,1) ) ),
VSTACK( header, return ) )