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 ) )
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))
)
Thank you, but unfortunately TOCOL(...,3) doesn't work since we have blank cells within data. For example no Vocal for some records. TOCOL() removes them, thus we have incorrect result.
SergeiBaklan good catch. simple fix:
=LET(in,Albums3D, headers,LetItBe!$A$1:$F$1, arr,HSTACK(in), stacked,DROP(REDUCE("",headers,LAMBDA(a,i, HSTACK(a,TOCOL(FILTER(DROP(arr,1),INDEX(arr,1,)=i,""), 0,1)))),,1), VSTACK(headers,FILTER(stacked,CHOOSECOLS(stacked,1)<>0,"")) )