Forum Discussion
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...
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) )
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)
)
This is a game changer. Thank you so much.