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

Interesting concept, passing the entire table as the first and only required argument, then unpivoting based on the optional arguments provided. Definitely looks doable as a lambda function.

I have a couple of custom functions already that I use for basic unpivots. The first one automatically removes blanks and errors, which is the most common request:

=LAMBDA(row_fields,col_labels,values,[scan_by_col],
   LET(
      tst, values<>"",
      fnλ, LAMBDA(arr,TOCOL(IFS(tst,arr),2,scan_by_col)),
      HSTACK(
         CHOOSEROWS(row_fields,fnλ(SEQUENCE(ROWS(row_fields)))),
         CHOOSEROWS(TRANSPOSE(col_labels),fnλ(SEQUENCE(,COLUMNS(col_labels)))),
         fnλ(values)
      )
   )
)

Set the optional [scan_by_col] argument to 1 or TRUE to change the output order.

The second one has the option to remove blanks and errors as desired:

=LAMBDA(row_fields,col_labels,values,[ignore],[scan_by_col],
   LET(
      v, TOCOL(values,,scan_by_col),
      a, HSTACK(
         INDEX(row_fields,TOCOL(IFNA(SEQUENCE(ROWS(row_fields)),values),,scan_by_col),SEQUENCE(,COLUMNS(row_fields))),
         INDEX(col_labels,SEQUENCE(,ROWS(col_labels)),TOCOL(IFNA(SEQUENCE(,COLUMNS(col_labels)),values),,scan_by_col)),v),
      CHOOSE(ignore+1,a,FILTER(a,NOT(ISBLANK(v))),FILTER(a,NOT(ISERROR(v))),FILTER(a,NOT(ISBLANK(v)+ISERROR(v))))
   )
)

The optional [ignore] argument accepts the same choices as that of TOCOL/TOROW:

• 0 - Keep all values (default)
• 1 - Ignore blanks
• 2 - Ignore errors
• 3 - Ignore blanks and errors

Both methods can handle multiple columns of row_fields, as well as multiple rows of col_labels.

In most cases I'm not too worried about headers as they're pretty easy to input/stack manually and customize on your own. If you wanted to get cute, though, and include an option for headers, it could look something like this:

=LAMBDA(row_fields,col_labels,values,[scan_by_col],[headers],
   LET(
      tst, values<>"",
      fnλ, LAMBDA(arr,TOCOL(IFS(tst,arr),2,scan_by_col)),
      pvt, HSTACK(
         CHOOSEROWS(row_fields,fnλ(SEQUENCE(ROWS(row_fields)))),
         CHOOSEROWS(TRANSPOSE(col_labels),fnλ(SEQUENCE(,COLUMNS(col_labels)))),
         fnλ(values)
      ),
      IF(
         TYPE(headers)=64,
         VSTACK(headers,pvt),
         IF(
            headers,
            VSTACK(HSTACK("Field"&SEQUENCE(,COLUMNS(row_fields)),"Attribute"&SEQUENCE(,ROWS(col_labels)),"Value"),pvt),
            pvt
         )
      )
   )
)

The optional [headers] argument would then accept the following:

• 0 or FALSE - None (default)
• 1 or TRUE - Auto-generate
• A horizontal array of custom headers, e.g. {"first","last","attribute","value"}

For example:

=Unpivotλ(A3:B5,C1:H2,C3:H5,TRUE,{"First","Last","Fiscal Year","Month","Commissions"})

Unpivot with multiple row and column fields

More advanced scenarios involving multiple value fields (e.g. Sales and Commissions) can get kind of hairy, so I just end up writing tailored solutions on a case-by-case basis for those situations.