Forum Discussion

brunomerola's avatar
brunomerola
Brass Contributor
Aug 29, 2022
Solved

Tabular Pivot Table with Excel Lambda and new array functions

Hello everyone,   I've been learning and practicing Lambda helper functions for the last few months and I'm amazed by advanced techniques, especially with those that handle arrays of arrays - Peter...
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Aug 29, 2022

    brunomerola 

    This might explain my hesitance getting involved with issues of layout!

    = LET(
        wildcards,      IFNA(HSTACK(UNIQUE(Data[Class]), "*"),"*"),
        distinct,       SORT(VSTACK(wildcards, UNIQUE(Data[[Class]:[Category]])),{1,2}),
        class,          CHOOSECOLS(distinct,1),
        category,       CHOOSECOLS(distinct,2),
        sumValue,       SUMIFS(Data[Value], Data[Class], class, Data[Category], category),
        outputClass,    IF(category="*", class, ""),
        outputCategory, IF(category<>"*", category, ""),
        output,         HSTACK(outputClass, outputCategory, sumValue),
        totalrow,       HSTACK("Total", "", SUM(Data[Value])),
        VSTACK(output, totalrow) 
     )

Resources